SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Bug on ROUND function?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ciupaz
Posting Yak Master

Italy
232 Posts
Posted - 03/15/2011 :  04:10:15  Show Profile  Reply with Quote
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

Norway
3271 Posts
Posted - 03/15/2011 :  04:35:51  Show Profile  Reply with Quote
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/
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2870 Posts
Posted - 03/15/2011 :  06:56:52  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts
Posted - 03/15/2011 :  06:59:59  Show Profile  Reply with Quote
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.
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts
Posted - 03/15/2011 :  07:29:39  Show Profile  Reply with Quote
Thank you all,
so I have to pay attention to the second parameter of the ROUND function.

Luigi
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts
Posted - 03/15/2011 :  07:35:19  Show Profile  Reply with Quote
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/
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts
Posted - 03/15/2011 :  07:44:05  Show Profile  Reply with Quote
Seems to show the same behavior whenever precision changes.
select round(0.96,1);
select round(0.996,2);
select round(0.9996,3);
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts
Posted - 03/15/2011 :  08:28:31  Show Profile  Reply with Quote
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/
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts
Posted - 03/15/2011 :  08:49:37  Show Profile  Reply with Quote
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/
Go to Top of Page

sqller
Starting Member

USA
1 Posts
Posted - 04/20/2011 :  18:35:03  Show Profile  Reply with Quote
"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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts
Posted - 04/20/2011 :  20:58:16  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000