Two conversions of practically the same number giving two different results

Question

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

This

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.

Answer 1

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.