[Solved] SQL Server Rounding issue

by


First check datatypes:

SELECT '0.458441', system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT  0.458441', NULL, 0) 
UNION ALL   
SELECT '10.000000', system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT  10.000000', NULL, 0)  
UNION ALL   
SELECT '5.000000', system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT  5.000000', NULL, 0);

╔═══════════╦══════════════════╗
║   value   ║ system_type_name ║
╠═══════════╬══════════════════╣
║ 0.458441  ║ numeric(6,6)     ║
║ 10.000000 ║ numeric(8,6)     ║
║ 5.000000  ║ numeric(7,6)     ║
╚═══════════╩══════════════════╝

Query 1:

SELECT (1+0.458441)*(1+10.000000/100)*(1+5.000000/100)
-- 1.684499355

Query 2:

SELECT (1 + @w) * (1 + @a/100.0) * (1 + @p/100.0)
-- 1.684499

After adding casting:

SELECT (1 + CAST(@w AS NUMERIC(8,6))) * 
       (1 + CAST(@a AS NUMERIC(8,6))/100.0) * 
       (1 + CAST(@p AS NUMERIC(8,6))/100.0)
-- 1.684499355

LiveDemo

Why it is the case: related article

4

solved SQL Server Rounding issue