Converting SQL FLOAT to SQL INT, lost data
57,953
This is the classic (int)((0.1+0.7)*10) problem. Because floats have arbitrary precision some data loss when casting to int is possible even for very simple cases.
Use ROUND(weight * 1000000.0, 0)
instead.
Author by
Anish Patel
Updated on July 09, 2022Comments
-
Anish Patel almost 2 years
I'm having some problems with converting some data stored in a FLOAT datatype to data stored in an INT datatype. The below example illustrates my problem:
DECLARE @data TABLE ( id INT, weight FLOAT ) INSERT INTO @data VALUES(1,0.015662) SELECT CAST(weight * 1000000 AS INT) FROM @data SELECT 0.015662 * 1000000 SELECT CAST(0.015662 * 1000000 AS INT)
The desired results would be:
ID = 1 VALUE = 15662
However when coming from the@data
table, I don't seem to get this. I instead getID = 1 VALUE = 15661.
Does anyone have any idea why this is? I'm guessing it's some sort of float nuance. But I never thought it would have a problem with something like the above. Any ideas? Thanks in advance for your help.
-
Lex over 13 yearsROUND(weight * 1000000.0, 0) would be better because we don't know which system author uses.