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.
| Author |
Topic |
|
watercloud
Starting Member
1 Post |
 Posted - 2010-02-03 : 10:11:57
|
| I run this T-SQL in SQL server 2005 and error:SELECT ROUND(99.99, -2)Error message is: An error occurred while executing batch. Error message is: Arithmetic Overflow.Can anyone explain this why this could happen?Thanks. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-03 : 10:35:42
|
| you're attempting to round 99.99 to 0.00, which exceeds the length you specified (2)change it to SELECT ROUND(99.99, -3) |
 |
|
|
Kanwulf
Starting Member
11 Posts |
Posted - 2010-02-03 : 11:29:38
|
| Actually it's about return types here. ROUND will return same datatype as the input. For example ROUND(99.99,2) will return the same error because our input (99.99) is a NUMERIC(4,2) so the result must also be NUMERIC(4,2). But the result should be 100.00 which is a NUMERIC(5,2) which doesn't fit, hence the overflow error. It's just like saying CAST(100.00 AS NUMERIC(4,2)).In your example ROUND(99.99,-2) should return 100.00 which obviously will not fit into a NUMERIC(4,2).When dealing with rounding operations I sometimes use CAST. For example instead of ROUND(99.99,2) you can use CAST(99.99 AS NUMERIC(4,1)).Ionut Hrubaru |
|
|
|
|
|
|
ROUND function return error