SQL Server 2K problem with fractional real values

[computer_prog]

I have a field of type Real in my SQL 2K database. I stored a value of
..35 in the field.

When I "Open Table"->"Return All Rows" in the Enterprise Manager I get
back .35 for the field value.

I went to The SQL Query Analyzer and executed the following T-SQL:

SELECT field
FROM table

I got back 0.34999999 for the field.

When running stored procs against the field I also get back 0.34999999.
This is causing problems in my app. I can use the Round T-SQL
statement to get back the value I expect, but this causes app
development problems. For a goof I put 1.35 in the field and T-SQL did
return 1.35. This problem only seems to occur with 0.nnn values. I
also tried a float data type for the field but I had the same problems
I had with real.

Why is T-SQL returning 0.34999999 for my field?

[Hugo Kornelis]

On 9 Mar 2005 13:24:08 -0800, co***********@hotmail.com wrote:

I have a field of type Real in my SQL 2K database. I stored a value of
.35 in the field. (snip)Why is T-SQL returning 0.34999999 for my field?

Hi computer_prog,

The datatypes real and float are for approximate numbers. The internal
representation precludes exact representation of many fractions. Just as
the value 1/3 can never be represented exactly as a fraction in our
decimal system, the value 35/100 can't be represented exactly in a float
or real datatype (but 25/100 can, incidentally).

If you need exact numeric precision, don't use float or real. Use
decimal or numeric instead.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)