Incorrect Handling of Real Numbers

Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
I wrong at this finding?
The below query is an example. (you can try any number of decimal
multiplications, or even sometimes decimal additions, but only seems to
affect 'REAL' numbers - numeric, decimal, money and float seem to do just
fine)
SELECT convert(real,.11) * CONVERT(real,3)
this returns 0.32999998 instead of .33.
This might not be a problem inherent to SQL as I get simular problems
handling real numbers from a Java based application, so I'm not sure if this
problem might be Windows or hardware based. I did this same test on several
different machines, (dual Xeons and P4 laptops) and every time it returns th
e
incorrect result.This issue also applies to the float data type. Real and float data types
can only store approximate numeric data because some values cannot be stored
precisely. Use decimal or money when exact decimal values are required.
From the Books Online:
<Excerpt href="http://links.10026.com/?link=createdb.chm::/cm_8_des_04_82ic.htm">
Approximate numeric (floating-point) data consists of data preserved as
accurately as the binary numbering system can offer. Approximate numeric
data is stored using the float and real data types in SQL Server. For
example, because the fraction one-third in decimal notation is .333333
(repeating), this value cannot be represented precisely using approximate
decimal data. Therefore, the value retrieved from SQL Server may not be
exactly what was stored originally in the column. Additional examples of
numeric approximations are floating-point values ending in .3, .6, and .7.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimbit" <Dimbit@.discussions.microsoft.com> wrote in message
news:1A0E0F28-5244-4C16-80EB-FC9B08B4B8B0@.microsoft.com...
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
> am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if
> this
> problem might be Windows or hardware based. I did this same test on
> several
> different machines, (dual Xeons and P4 laptops) and every time it returns
> the
> incorrect result.|||use decimal or numeric
read "DATA TYPES" in BOL
decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.
numeric
Functionally equivalent to decimal.
- - - Approximate Numerics - - -
float
Floating precision number data with the following valid values: -1.79E + 308
through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.
real
Floating precision number data with the following valid values: -3.40E + 38
through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Dimbit" wrote:

> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if th
is
> problem might be Windows or hardware based. I did this same test on sever
al
> different machines, (dual Xeons and P4 laptops) and every time it returns
the
> incorrect result.