ROUND, is there another way to do this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

ROUND, is there another way to do this?

Author

Topic

brandonl
Yak Posting Veteran

58 Posts

Posted - 2002-10-10 : 19:43:51

I have a select statment:

ROUND(CAST(CAST(PointsFielded AS float) / CAST(13) As Float), 1) AS PointsPerGame

Some of them will return 24.0 or 24.1 for example. But, then there are a few that are returning: 21.199999999999999

I'd rather not do the rounding on the vbscript output side, as it'll take off the .0 (if there is one) and just return 24. The SQL output is returning 24.0 and that's the format I want.

Why do some of them return the .9999999999? Is that just a limitation of round, or is there something better I could do?

~BrandonL

SamC
White Water Yakist

3467 Posts

Posted - 2002-10-10 : 23:00:27

Your select statement doesn't parse correctly, but I think you meant to post:

select ROUND(CAST(PointsPerGame AS float) / CAST(13 As Float), 1) AS PointsPerGame

If you stick with floating point results, but control the format of the displayed result, those numbers like 21.199999 would display as 21.2.

Having said this, it's interesting and inexplicable to me that

select round(21.188, 1)

displays as 21.2, not 21.19999 in other words, rounding should have predictable results. I wonder if anyone can explain why round works differently? I'm wondering if 21.188 is a different precision than 'float'?

Sometimes, when an *exact* number is needed, casting the result to integer is a better way to go

CAST ((YourResult + 0.5) * 10.0 AS INTEGER)

Would always return an integer, with no trailing .9999999 but you need to be aware you're working with 10x the value you need. This is more work to deal with than float, but it is precise and predictable.

Sam

brandonl
Yak Posting Veteran

58 Posts

Posted - 2002-10-11 : 00:52:26

quote:

Having said this, it's interesting and inexplicable to me that

select round(21.188, 1)

displays as 21.2, not 21.19999 in other words, rounding should have predictable results. I wonder if anyone can explain why round works differently? I'm wondering if 21.188 is a different precision than 'float'?

Thanks for the SELECT correction-I did put it in wrong.

However, here's some sample data that I'm getting this problem with:

PF
275
376

The Week is 13, so 275/13=21.153846153846153846153846153846 yet the query is returning 21.199999999999999 with the ROUND(query,1) around it. Same sort of issue is happening with the 276 value. However, 358 is showing up as 27.5-which is right.

~BrandonL

ashok
Yak Posting Veteran

57 Posts

Posted - 2002-10-11 : 03:11:08

You should not be using a float datatype.
A float datatype is known as an approximate datatype.
So when you round() a float value it returns an approximate
value and not the exact value (which is what you want)
To quote BOL :

"Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. "

use a numeric or decimal datatype.

-ashok
http://www.unganisha.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources