Converting SQL FLOAT to SQL INT, lost data

Question

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 get ID = 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.

Answer 1

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.

Answer 2

This is common behaviour for float data type due to specificity of float on computers. Use decimal (number) data type with fixed digits after decimal point. For example, decimal(10, 6).