Hello,

When creating a table with two floats, inserting in two values, and then selecting their product, I get unexpected, incorrect values. When rounded to the nearest penny, they are off by one penny - thus ticking off our accounting people. The result should be ending in an even half a cent that would be rounded up, but instead it comes out as .499999 of a cent and is rounded down.

Here is a little example script that duplicates the problem. SQL Server (version 7) also is converting 0.785 to 0.78500000000000003 in this example, but that is not what is causing the final problem, though perhaps it is related (as you can not from the .785*float2 product in the example below).

Using decimals instead of floats could fix this problem, but unfortunately it is 3rd party and changing the DB structure is a difficult proposotion for us.

Anyhelp would be greatly appreciated!

Thanks,
-Matthew Caffrey
matthew.caffrey@compaq.com
matthewcaffrey@yahoo.com
Houston, TX


CREATE TABLE dbo.float_test (
float1 float NULL ,
float2 float NULL
)

INSERT into dbo.float_test (float1, float2) values (.785, 169.0 )

select float1, float2, float1*float2, 0.785*float2, 0.78500000000000003*169.0 from dbo.float_test

Results:
0.78500000000000003 169.0 132.66499999999999 132.66499999999999
132.665000000000005070