Precision is lost when doing cast of double value to decimal in SQL Server
0 down vote favorite I am trying to retrieve a field value which is of type double in SQL Server and then casting it to decimal with scaling factor 13 and 14. If the scale is 13, it is giving proper result. If the scale is 14, the value is getting changed. I am using the below queries and their respective results. Query: SELECT CAST(discount AS DECIMAL(30,13)) as real_value, discount FROM pricetable WHERE ref = '123' Result: (Decimal('123.9900000000000'), 123.99) Query: SELECT CAST(pricepaid AS DECIMAL(30, 14)) as real_value, pricepaid FROM pricetable WHERE ref = '123' Result:(Decimal('115.84999999999999'), 115.85) Why does casting double to decimal with different scaling factors produce different results? Please let me know how to fix this issue.
That is because a double is an approximative datatype, while decimal/numeric is exact. If you look at decimal(38,35)-representation of 123.99 you will see that it is actuall stored as 123.98999999999999488409230252727866173 And 123.98999999999999488409230252727866173 rounded to 13 decimals is 123.9900000000000 While 114.85 is stored as 115.84999999999999431565811391919851303 which rounded to 14 decimals is 115.8499999999999. Because the 15th decimal is a 4, so the value gets rounded down when casting. If you cast to 13 decimals, you'll have a 14th decimal with a value 9, so casting to 13 decimals gets rounded up to 115.85.