Rounding a numeric value to nearest integer

Question

Rounding a numeric value to nearest integer

khpcbgnt

SSC Veteran

Points: 270
More actions
January 3, 2018 at 1:40 pm

#334321

Hi ,
I am trying to round the following numbers to nearest integer.
SELECT ROUND(9.6,0)
SELECT ROUND(99.6,0)
The above two statements failing with the following error.
Msg 8115, Level 16, State 2, Line 30
Arithmetic overflow error converting expression to data type numeric.
The following two statements are working fine.
SELECT ROUND(19.6,0)
SELECT ROUND(199.6,0)
Is there a common syntax that I can use to round all the 4 above values to the nearest integer?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

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.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

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.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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.

I agree.