March 26, 2010 at 1:15 am
SELECT ROUND( -0.6401, 0 )
The above statement gives me an error saying An error occurred while executing batch. Error message is: Arithmetic Overflow. I get the error for any number between -0.5000 and -0.9999
Has it got something to do with latest patches not being installed or something?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 26, 2010 at 1:20 am
Hi there,
Try CASTing them into numeric/decimals first
SELECT ROUND( CAST(-0.6401 AS DECIMAL(10,4)), 0 )
SELECT ROUND( CAST(-0.6401 AS DECIMAL(10,4)), 1 )
SELECT ROUND( CAST(-0.6401 AS DECIMAL(10,4)), 2 )
SELECT ROUND( CAST(-0.6401 AS DECIMAL(10,4)), 3 )
SELECT ROUND( CAST(-0.6401 AS DECIMAL(10,4)), 4 )
Hope this helps
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
March 26, 2010 at 1:26 am
Thanks Quatrei, that worked. Good work around to solve the problem.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 26, 2010 at 1:29 am
^__^ Welcome... Glad I could help!
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
March 26, 2010 at 2:19 am
Kingston Dhasian (3/26/2010)
SELECT ROUND( -0.6401, 0 )The above statement gives me an error saying An error occurred while executing batch. Error message is: Arithmetic Overflow. I get the error for any number between -0.5000 and -0.9999. Has it got something to do with latest patches not being installed or something?
No, it is because the literal value -0.6401 is assigned a type just large enough to hold it, in this case DECIMAL(4, 4). When the result rounds to -1, you get an overflow error because -1 does not fit in a DECIMAL(4,4) - all the available precision is taken up by the scale.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 26, 2010 at 3:07 am
I had actually googled some information about the error but i was of the impression that the number -0.6401 was of type DECIMAL(5,4). I didn't know SQL Server was considering it as DECIMAL(4,4). Thanks for the additional information Paul. Is there a way in SQL Server by which i can get the datatype from the value given?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 26, 2010 at 3:14 am
Kingston Dhasian (3/26/2010)
I had actually googled some information about the error but i was of the impression that the number -0.6401 was of type DECIMAL(5,4). I didn't know SQL Server was considering it as DECIMAL(4,4). Thanks for the additional information Paul. Is there a way in SQL Server by which i can get the datatype from the value given?
Only indirectly:
1. Use SELECT INTO to create a table with a column created from the literal (with an alias) and look at the column definition
2. Define the literal as a computed column, and again, check the definition
3. Variations on the above...
Also, SQL_VARIANT_PROPERTY can be hacked around to do it in 2008.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 26, 2010 at 4:02 am
Thats what i was looking for. But sadly i don't have SQL Server 2008. I can use your other methods till then. Thanks once again Paul.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 30, 2010 at 12:05 pm
No worries 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply