SQL ROUND function on a FLOAT

[philk12]

This isn't so much a question but more like a "what the?!".

If I have the float value "3.4173900000000001E+000" and issue a "ROUND( 3.4173900000000001E+000, 2 )", I get (drum roll) 3.4199999999999999E+000.
Cast as a dec with 2 decimal places and viola! You get 3.41739 rounded to 3.41.

This statement shows it all.

SELECT FLOAT(3.41739) AS NUM,
ROUND( FLOAT(3.41739), 2 ) AS ROUNDED_NUM,
CAST( ROUND( FLOAT(3.41739), 2 ) AS DEC(5,2) )
AS CASTED_ROUNDED_NUM
FROM SYSIBM/SYSDUMMY1

Output:
NUM ROUNDED_NUM CASTED_ROUNDED_NUM
3.4173900000000001E+000 3.4199999999999999E+000 3.41

Of course I know to cast it to 3 decimal places before the round, but still...

 

[gmmastros]

Float and real are approximate data types.

float and real
Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.

It's the same type of issue as representing 1/3 as a decimal (0.3333 and then just keep going with the 3's).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

Never use float or real for anything that you do math calculations on because it is always an approximate value. It would be best tot convert the data type to decimal. If you can;t then you might have to use cast or convert in your SQl, but you will still introduce errors do to the approximate nature of the datatype you began with. Not a big problem with a few records that are added together but with large record sets or dvision in particualr the errors can creep and becasue the data set is large, they won;t be noticed becasue nobody is hand checking the calculations. Again, I never ever use these datatypes if I will want to do math.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief