TSQL Round() inconsistency?

Question

The problem we have is reduced to the following two statements:

select convert(float, (convert(float,5741.61)/convert(float, 196.00)) * convert(float,14.00)) as unrounded, round(convert(float, (convert(float,5741.61)/convert(float, 196.00)) * convert(float,14.00)), 2)  as roundedTo2dp

select convert(float, 410.115) as unrounded, ROUND( convert(float, 410.115), 2) as roundedTo2dp

The first statement uses floats to calculate a value of 410.115, and also that result with a round() to 2 decimal places. The rounded value comes out at 410.11.

The second statement uses the float value 410.115 and also rounds it to 2 decimal places. The rounded result comes out as 410.12.

Why is one rounding down and the other rounding up when the value being rounded it the same?

How can I get the first statement to round to 410.12?

EDIT: apologies for formatting -- stackoverflow isn't showing any formatting on this machine (very odd).

Answer 1

Decimals are better with precision than floats. If you changed up the float to be something like DECIMAL(18,2), you'll get what you are expecting and you don't need to call the round function anymore.

select convert(decimal(18,2), (convert(decimal(18,2),5741.61)/convert(decimal(18,2), 196.00)) * convert(decimal(18,2),14.00)) as unrounded, round(convert(decimal(18,2), (convert(decimal(18,2),5741.61)/convert(decimal(18,2), 196.00)) * convert(decimal(18,2),14.00)), 2)  as roundedTo2dp

results in

unrounded   roundedTo2dp
410.12      410.12

Link to the MSDN about decimals. http://msdn.microsoft.com/en-us/library/ms187746.aspx

Hope that helps...

Answer 2

The numbers are not equal:

SELECT  CAST(convert(float, (convert(float,5741.61)/convert(float, 196.00)) * convert(float,14.00)) AS BINARY(8))
UNION ALL
SELECT  CAST(convert(float, 410.115) AS BINARY(8)) as bin

----
0x4079A1D70A3D70A3
0x4079A1D70A3D70A4