Hugo Kornelis (8/24/2010)

---

Here is an interesting experiment (works on every client):

SELECT ROUND(789.98,-3) AS WeirdCol
INTO WeirdTable;
go
EXEC sp_help 'WeirdTable';
go
UPDATE WeirdTable
SET WeirdCol = WeirdCol - 0.001;
go
DROP TABLE WeirdTable;
go

Hugo Kornelis (8/24/2010)

---

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);

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?

hrvoje.piasevoli (8/24/2010)

---

Hugo what is the expected result? On my system it's an empty table (nothing weird about the table though ) and the update operates on 0 rows - therefore nothing is really happening further. Are you referring to the fact that the table gets created despite the arithmetic overflow?

Regards,

Hrvoje Piasevoli

hrvoje.piasevoli (8/24/2010)

---

Hugo what is the expected result? On my system it's an empty table (nothing weird about the table though ) and the update operates on 0 rows - therefore nothing is really happening further. Are you referring to the fact that the table gets created despite the arithmetic overflow?

Regards,

Hrvoje Piasevoli

paul.knibbs (8/24/2010)

---

Nope, just tested it on a SQL 2008 Express install and it came up with an arithmetic overflow as expected.

Hugo Kornelis (8/24/2010)
Super! Thanks for testing and reporting back here.
Obviously, this weird behaviour that appears to be exclusive to the ROUND() function has been fixed in SQL Server 2008.