Rounding a numeric value to nearest integer

  • 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.

  • 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.

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

  • 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.

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

  • 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.

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

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