ziangij (8/23/2010)
plz. refer http://technet.microsoft.com/en-us/library/ms175003.aspxfor
select ROUND(748.58, -3)
result displayed is 1000.00
but when i execute the same i get an error:
An error occurred while executing batch. Error message is: Arithmetic Overflow.
any suggestions plz. ?
I get the same error. The explanation is simple, and already given by tommyh: "748.58" is considered to be numeric(5,2), and the result of ROUND is the same data type as its input. But 1000.00 does not fit in a numeric(5,2).
The fix is simple - use an explicit CAST to get a data type that does have some room for expansion:
SELECT ROUND(CAST(748.58 AS decimal(6,2)), -3);
tommyh (8/24/2010)
With every setting at the default however SQL allows this. Now i havent been able to replicate the error.
Frankly, this surprises me a lot more than the error ziangij gets. (And that I can replicate). As stated, I do get the same error message. (SQL 2005, SP3). What version did you test this on?