## Rounding a numeric value to nearest integer

 Author Message khpcbgnt Valued Member Group: General Forum Members Points: 62 Visits: 13 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 30Arithmetic 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. Lynn Pettis SSC Guru Group: General Forum Members Points: 391551 Visits: 42844 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 PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) Jeff Moden SSC Guru Group: General Forum Members Points: 873062 Visits: 47516 +xLynn Pettis - Wednesday, January 3, 2018 2:00 PMMaybe 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Lynn Pettis SSC Guru Group: General Forum Members Points: 391551 Visits: 42844 +xJeff Moden - Wednesday, January 3, 2018 6:29 PM+xLynn Pettis - Wednesday, January 3, 2018 2:00 PMMaybe 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 PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) Jeff Moden SSC Guru Group: General Forum Members Points: 873062 Visits: 47516 +xLynn Pettis - Wednesday, January 3, 2018 6:58 PM+xJeff Moden - Wednesday, January 3, 2018 6:29 PM+xLynn Pettis - Wednesday, January 3, 2018 2:00 PMMaybe 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Lynn Pettis SSC Guru Group: General Forum Members Points: 391551 Visits: 42844 +xJeff Moden - Wednesday, January 3, 2018 7:39 PM+xLynn Pettis - Wednesday, January 3, 2018 6:58 PM+xJeff Moden - Wednesday, January 3, 2018 6:29 PM+xLynn Pettis - Wednesday, January 3, 2018 2:00 PMMaybe 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. Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)