Bug on ROUND function? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Bug on ROUND function?

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2011-03-15 : 04:10:15

Hi all,
why if I write:

SELECT ROUND(0.6, 0)

I got this error:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type numeric.

I'm using SQL Server 2008 version 10.50.1600.

Why this happen?

Thanks a lot.

Luigi

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-15 : 04:35:51

This is weird...I'm sure there is some kind of explanation but I really don't get it. From my simple tests it seems that rounding any value >= 0.5 and < 1 will give an error.

This works:
SELECT ROUND(0.15, 0)
SELECT ROUND(0.4999999999, 0)
SELECT ROUND(1.6, 0)

This doesn't
SELECT ROUND(0.5, 0)
SELECT ROUND(0.6, 0)
SELECT ROUND(0.9999999999, 0)

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-15 : 06:56:52

SELECT ROUND(0.6, 1)
Works. Maybe it's considering 0.6 a numeric(1,1) and can't round it up?

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-15 : 06:59:59

Here is an explanation of it. http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/77929/Weird-Rounding-Error
If that is true, round(9.6,0) should also show the same problem, which it does.

Ciupaz
Posting Yak Master

232 Posts

Posted - 2011-03-15 : 07:29:39

Thank you all,
so I have to pay attention to the second parameter of the ROUND function.

Luigi

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-15 : 07:35:19

That is completely fubar! What this basically means is that the ROUND function will throw an error if the rounding causes the precision to change. Even though this makes sense in terms of datatypes it is completely unacceptable to have some rounding operations fail while others work. Baffling...

Does this occur for other lengths than 0? Seems like I'm incapable of thinking of any examples at the moment...

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-15 : 07:44:05

Seems to show the same behavior whenever precision changes.

select round(0.96,1);
select round(0.996,2);
select round(0.9996,3);

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-15 : 08:28:31

quote:
Originally posted by lionofdezert

try this
select round(0.96,1,1);
select round(0.996,2,1);
select round(0.9996,3,1);

This is truncation/flooring, not rounding.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-15 : 08:49:37

It seems like there are several issues not only with the ROUND function but also many of the other math functions. Look at this (highly interesting) thread and especially the response from Microsoft all the way at the end:

-> http://social.msdn.microsoft.com/Forums/en/transactsql/thread/caef589d-a464-4d3e-88fb-cae3e64aeb68

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

sqller
Starting Member

1 Post

Posted - 2011-04-20 : 18:35:03

"Return type of round() is the same as the input", according to link posted by sunitabeck (http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/77929/Weird-Rounding-Error).
So, i did this - select round(cast(0.6 as float),0) and got 1. No error.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-20 : 20:58:16

I think that is consistent with the "Return type of round() is the same as the input" statement. Because you did the casting, input type now is float and so the return type is float as well.

When you don't do the casting, 0.6 is of type decimal(1,1). So the return type is also decimal(1,1), which can hold only a maximum of 0.9.

It is a chore to have to cast decimals to float (or to a larger decimal type) before rounding - not to mention precision issues with float etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources