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 1Arithmetic 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'tSELECT ROUND(0.5, 0) SELECT ROUND(0.6, 0) SELECT ROUND(0.9999999999, 0)- LumbagoMy 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?JimEveryday I learn something that somebody else already knew |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
|
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...- LumbagoMy 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 thisselect round(0.96,1,1);select round(0.996,2,1);select round(0.9996,3,1);
This is truncation/flooring, not rounding.- LumbagoMy 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- LumbagoMy 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. |
|
|
|