Issue with float data type

Hi,

I have a table with float data type. When we insert or update data with some math(1+1.01) it converts the decimal value to approximate value.

Example:

Create a table say "Table1" with field "Field1" of data type float

Now insert a record with value 1.01

Select * from Table1 would give me out put at 1.01

Update Table1 set Field1 = 1.01+1

Now Select * from Table1

Instead of returning me 2.01, it returns 2.0099999999999998. Same thing happens if I insert record with following statement

insert into Table1(Field1) values (1+1.01)

Any idea why its doing this way? I know float is approximate data type, but shouldn't it be giving me correct value for what I am doing? Is there any server setting that I can change to get proper result?

Thanks,

Hiten:

I think the answer to your question is that there is no exact conversion of the number 2.01 into a hexidecimal floating point representation; what I remember is that it is initially surprising what numbers do not convert exactly and this is probably one of them. I think the answer is you need to live with the limitations of floating point representation or switch to an exact representation with a pre-defined precision and scale. If you stick with float, use of the ROUND function will be helpful. I verified and I receive the same behavior and same response that you do. Somebody check me on this?

Dave

|||

Hiten:

I found this article:

http://docs.python.org/tut/node16.html

The "B.1" section discusses the problem of representing 1/10 in binary; this should give the idea.

Dave

|||

Float is an approximate data type so it stores only close approximation of a value. See below link for more details:

http://msdn2.microsoft.com/en-us/library/ms187912.aspx

You need to use decimal or numeric to get exact representation of the value.

|||

Hi all,

What are the drawbacks of using decimal insted of float data type?

Thanks.