Menu
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.
244
Convert a float to an int always results in a data loss. The trunc() function returns the integer part of a number. The floor() function returns the largest integer less than or equal to a number.
Convert Float to Int In this example, we will convert a float data type to integer. In the following query, we will declare a variable that data type is float and then we will use the SQL CONVERT function in order to convert float value to integer so for that we will perform data converting operation.
A float value can be converted to an int value no larger than the input by using the math. floor() function, whereas it can also be converted to an int value which is the smallest integer greater than the input using math.
Float stores an approximate value and decimal stores an exact value. In summary, exact values like money should use decimal, and approximate values like scientific measurements should use float. When multiplying a non integer and dividing by that same number, decimals lose precision while floats do not.
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.
137
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
