asked
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.
In the first query, you can see I am retrieving discount value from the pricetable which is 123.99 and converting it to decimal with a scale of 13 and the result is Decimal('123.9900000000000'). In the second query, I am retrieving pricepaid value which is 115.85 and converting it to decimal with a scale of 14 and the result is Decimal('115.84999999999999'). discount and pricepaid values are stored as double in sql server tables.
It is giving correct result if I a give a scale in between 1 to 13. The value is changing if the scale is more than 13
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.
If you store 115.85 as a double, the actual stored value of that will be 115.84999999999999431565811391919851303..... If you ever need to rely on the values stored in a numeric datatype, never use float or double, they are just approximations. Even if they are damn-well almost the exact value you write, they are actually a small fraction away from the exact value you intended to store. Therefore a float or double should never be used to store exact values like financial transactions. But for values which are truly approximative, like a measure from a thermometer, a float or double works fine.
@AkhilKumar The precision of the double data type, which corresponds to float(53) in SQL Server is only 15 digits (this is grand total, with the groceries). The precision of the single data type, which corresponds to the float(24), a.k.a. real in SQL Server is 7 digits. As @Magnus Ahlkvist said, the practice of storing exact numbers using approximate data types is ill advised and should be avoided if at all possible, particularly when the data is related to any financial/sales related calculations. If the stored numbers don't have to be exact then I guess it is OK to use double (float) (I would never, ever do it anyway, no matter what). Decimal data type on the other hand provides up to 38 precision, so if you need 14 scale, you can still go all the way up to decimal(38, 14), which will take 17 bytes per number stored with 24 digits before the decimal point and 14 - after, or you can reduce it to decimal(28, 14) with 14 digits before and 14 digits after, taking 13 bytes for storage. If your numbers are rather small (less then 100K) then you can even opt for decimal (19, 14) still with the whopping 14 digits after the decimal point and only taking 9 bytes for storage.
if we want to store decimal('115.85') in the sql server as double datatype. what will be the value
21 People are following this question.
