Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ROUND function and arithmetic overflow

In MS SQL Server, if I

SELECT ROUND(9.4, 0), ROUND(8.6, 0), ROUND(10.6, 0)

I unsurprisingly get:

9.0 9.0 11.0

But if I do

SELECT ROUND(9.6, 0)

I get:

Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type numeric.

I know I can just CAST(9.6 as DECIMAL(10,0)), but what is going on here?

TIA

like image 216
mnmdad Avatar asked Oct 26 '25 20:10

mnmdad


2 Answers

SQL takes the first parameter as the datatype, which is, in this case DECIMAL(2,1). The expected outcome, 10.0, should be of type DECIMAL(3,1) which is why you get the error.

Try:

SELECT ROUND(cast(9.6 as decimal(2,1)), 0)

then try:

SELECT ROUND(cast(9.6 as decimal(3,1)), 0)
like image 107
Sam cd Avatar answered Oct 28 '25 10:10

Sam cd


When you round up from 9.6 it needs an extra digit to store the number. For literal decimal values you'll have to cast to something wider before doing the round operation. You're probably expecting this to be treated as a floating point value. If you try round(9.6e, 0) you'll see a different behavior.

Play around with sql_variant_property to see more details. And apparently ceiling() and floor() don't have the same problem (maybe because they only output integer and zero-scale decimals?). It seems that those two functions keep the same precision but slide the scale up to zero which always leave enough room for the potential new place.

like image 25
shawnt00 Avatar answered Oct 28 '25 11:10

shawnt00



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!