CODEWITHSUNDEEP
sqlcastingfloating-pointinteger
Anish Patel
Anish Patel

Reputation: 767

Converting SQL FLOAT to SQL INT, lost data

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.

Upvotes: 8

Views: 60176

Answers (2)

Alin P.
Alin P.

Reputation: 44346

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.

Upvotes: 16

Lex
Lex

Reputation: 1378

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).

Upvotes: 0

Related Questions