TSQL Round () mismatch?

Question

The task that we have comes down 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 operator uses float to calculate the value 410.115, as well as the result with rounding () to 2 decimal places. The rounded value is displayed at 410.11.

The second statement uses a float value of 410.115, and also rounds it to two decimal places. The result of rounding is obtained as 410.12.

Why is one rounding down and another rounding when the value is rounded the same way?

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

EDIT: apologies for formatting - stackoverflow does not show any formatting on this computer (very strange).

Answer 1

Decimal values ​​are better with precision than floats. If you change the float to something like DECIMAL (18,2), you get what you expect and you no longer need to call the round function.

 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 

leads to

 unrounded roundedTo2dp 410.12 410.12 

MSDN reference for decimal places. http://msdn.microsoft.com/en-us/library/ms187746.aspx

Hope this 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 

Answer 3

'float' is an approximate number data type, and therefore not all values ​​in a range of data types can be represented exactly.

This is based on http://msdn.microsoft.com/en-us/library/ms173773.aspx .

I believe this is why there is a rounding issue when using float values. You can never be 100% sure!

Ex. Select round (convert (float, 1.5555), 2) - Gives 1.56

Choose a round (convert (float, 1.555), 2) - Gives 1.55!

With such a prime, there is a difference in the expected result when using float.