To my surprise, I run the following queries in SQL Server 2014 and each returns a different result, as shown in this db<>fiddle...


Select convert(numeric(19,2),convert(float,9.76499999999999999999999999))

gives 9.76.

And this

Select convert(numeric(19,2),convert(float,9.7649999999999999999999))

gives 9.77.

Can someone explain why one truncates while the other rounds up? This is causing a nightmare for me.

I wish I could just stop using floats but they are all over our application, which has been ongoing for over 30 years.


For what it's worth, this behaviour changes in SQL Server 2016. When I try against 2017 with COMPATIBILITY_LEVEL = 120 I see your observed behaviour. When COMPATIBILITY_LEVEL = 130 both input values produce the same rounded output value 9.77.

There is some documentation here but it is not conclusive. I do not have 2014 documentation to hand but it would be interesting to compare for differences.

You may find some helpful information in the Microsoft Support article SQL Server and Azure SQL Database improvements in handling some data types and uncommon operations.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.