SEO

#1 (**permalink**) 02-29-2008, 06:32 AM

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?

#2 (**permalink**) 02-29-2008, 06:32 AM

On 9 Mar 2005 13:24:08 -0800, computer_prog@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)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

SQL Server 2K problem with fractional real values

$SQL Server 2K problem with fractional real values$ SQL Server 2K problem with fractional real values