Jeff Moden - Wednesday, January 3, 2018 7:39 PM

Lynn Pettis - Wednesday, January 3, 2018 6:58 PM

Jeff Moden - Wednesday, January 3, 2018 6:29 PM

Lynn Pettis - Wednesday, January 3, 2018 2:00 PM

Maybe this will shed some light on the subject:

DECLARE @testnum DECIMAL(18,2);
SET @testnum = 9.6;
SELECT ROUND(9.6,0);
SELECT ROUND(@testnum,0);
SET @testnum = 99.6;
SELECT ROUND(99.6,0);
SELECT ROUND(@testnum,0);

SET @testnum = 19.6;
SELECT ROUND(19.6,0);
SELECT ROUND(@testnum,0)
SET @testnum = 199.6;
SELECT ROUND(199.6,0);
SELECT ROUND(@testnum,0);

basically, the first two are trying to add an additional digit of precision to the numeric value while the second two aren't.

Since the result of round is DECIMAL(38,s) for decimal or numeric and FLOAT for float values according to Books Online, this sounds like a serious flaw for the ROUND function.  It's amazing to me that after more than 20 years of using the function, I've apparently not ever used it with literals before.

Actually Jeff, I think it is defaulting to the size of the literal values and that is why it is failing because it can't expand the size of the data type.

I did a SELECT/INTO with the literals and they came out as DECIMAL(9,1) in the table (5 bytes, 9 places of precision, 1 place of scale).

The real problem is... it doesn't work as expected and there's nothing in the documentation that says it shouldn't.  It should be able to handle these problems, literal or not.