Odd output with SELECT ROUND(VAR)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Odd output with SELECT ROUND(VAR)

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-04-18 : 11:39:33

When pulling REAL formatted numeric data using the ROUND() function, I'm seeing unexpected amts in output when the thousandths place is a 5. Any ideas?

Example:

TABLE
PRCES: Composite Pricing
Close_ real

QUERY
SELECT ROUND(PRCES.CLOSE_,2) AS ROUNDED_CLOSE
, PRCES.CLOSE_
FROM PRCES

OUTPUT
ROUNDED_CLOSE CLOSE_
70.0 70.0
70.0 70.0
69.629999999999995 69.625
69.5 69.5
70.0 70.0
68.629999999999995 68.625

I've tried to reproduce this using transact SQL, but can't:
DECLARE @SOMENUM REAL
SET @SOMENUM = 69.625
PRINT ROUND(@SOMENUM,2)

OUTPUT
69.63

Thanks in advance!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-18 : 11:52:00

It converts 69.625 to 2 dec places = 69.63
It outputs that as a real as PRCES.CLOSE_ is a a real.
69.23 cannot be represented exactly as a real so 69.629999999999995

try
select round(convert(real, 69.625),2)
select round(69.625,2)
select convert(real,69.625)
select convert(real,69.63)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-04-18 : 16:35:02

Oddest thing... when run in the SQL Server 2005, the problem goes away.

So was this a bug that was corrected or was 2000 mathematically correct and they fudge it for 2005 because the reinterpretation was not practical?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources