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
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)
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With