double type problem with SQL server

[francois]

Hi,

I have some fields in my database that are of the float DB type (I am using
SQL Server 2000)

When I get the values from the DB in my C# code i do something like

SqlDataReader reader;
....
double dValue = (double) reader["price"];

let say the value of the price field is O.92 in the DB. And to make sure of
it I just run and SQL update in the DB, using Sql Query analyzer to ensure
that the values i well 0.92.

When I run the code, if I have the following test :
bool b = (dValue == 0.92);
SOMETIMES b will be true and sometimes it will be false.

If I debug I see that sometimes dValue is 0.92 and sometimes something like
0.920000000000004
Moreover if I run the query analyzer and run the select statement in it, I
also have the same problem getting the weird 0.920000000000004 value
displayed.

Then I assume the problem is on the SQL server side. But I assume that as it
is such basic functionality there must exist a solution for it isn't it? Can
anyone help me on this it would be VERY appreciated.

Best regards too all readers.

Francois.

 

[Jon Skeet [C# MVP]]

I have some fields in my database that are of the float DB type (I am using
SQL Server 2000)

When I get the values from the DB in my C# code i do something like

SqlDataReader reader;
...
double dValue = (double) reader["price"];

let say the value of the price field is O.92 in the DB. And to make sure of
it I just run and SQL update in the DB, using Sql Query analyzer to ensure
that the values i well 0.92.

When I run the code, if I have the following test :
bool b = (dValue == 0.92);
SOMETIMES b will be true and sometimes it will be false.

If I debug I see that sometimes dValue is 0.92 and sometimes something like
0.920000000000004
Moreover if I run the query analyzer and run the select statement in it, I
also have the same problem getting the weird 0.920000000000004 value
displayed.

Then I assume the problem is on the SQL server side. But I assume that as it
is such basic functionality there must exist a solution for it isn't it? Can
anyone help me on this it would be VERY appreciated.

There's no problem (necessarily) on the SQL Server side - there *is* a
problem with comparing floating point values directly like this.
Depending on what the JIT is doing, the value may be stored in 64 bits
or 80 bits (the latter if it's in a register, basically) which will
make it more accurate - but change what it will compare with,
obviously.

The usual suggestion for floating point numbers is to compare within a
tolerance range.

See http://www.pobox.com/~skeet/csharp/floatingpoint.html for more
information.

I would suggest storing prices as decimals instead of binary floating
point numbers, however.

 

[francois]

Then if I understand well, the only thing that was to understand is that for
many "exact" decimal value as let say 0.92 it may not be possible to store
it as an "round" value in binary format which brings the problem I arised in
my post. And we have the same thing of something like a third (1/3) for
decimal numbers.
Isn't it?

Did I understand the thing well?

Francois.

I have some fields in my database that are of the float DB type (I am using
SQL Server 2000)

When I get the values from the DB in my C# code i do something like

SqlDataReader reader;
...
double dValue = (double) reader["price"];

let say the value of the price field is O.92 in the DB. And to make sure of
it I just run and SQL update in the DB, using Sql Query analyzer to ensure
that the values i well 0.92.

When I run the code, if I have the following test :
bool b = (dValue == 0.92);
SOMETIMES b will be true and sometimes it will be false.

If I debug I see that sometimes dValue is 0.92 and sometimes something like
0.920000000000004
Moreover if I run the query analyzer and run the select statement in it, I
also have the same problem getting the weird 0.920000000000004 value
displayed.

Then I assume the problem is on the SQL server side. But I assume that as it
is such basic functionality there must exist a solution for it isn't it? Can
anyone help me on this it would be VERY appreciated.

There's no problem (necessarily) on the SQL Server side - there *is* a
problem with comparing floating point values directly like this.
Depending on what the JIT is doing, the value may be stored in 64 bits
or 80 bits (the latter if it's in a register, basically) which will
make it more accurate - but change what it will compare with,
obviously.

The usual suggestion for floating point numbers is to compare within a
tolerance range.

See http://www.pobox.com/~skeet/csharp/floatingpoint.html for more
information.

I would suggest storing prices as decimals instead of binary floating
point numbers, however.

 

[Jon Skeet [C# MVP]]

Then if I understand well, the only thing that was to understand is that for
many "exact" decimal value as let say 0.92 it may not be possible to store
it as an "round" value in binary format which brings the problem I arised in
my post. And we have the same thing of something like a third (1/3) for
decimal numbers.
Isn't it?

Did I understand the thing well?

I think so.

0.92 can't be represented exactly as a binary floating point number.
Depending on exactly what the JIT is doing, it may be approximated as a
64 bit double or an 80 bit extended double - and when you compare one
of those with the other, they're not going to be equal. That's why
you're seeing the problem you are.

However, the other important thing to think about is whether a binary
floating point number is really the most appropriate data type to use
in your database in the first place - and for prices, it's usually not.