Why does T-SQL throw error on rounding a specific value

Question

In SQL Server, when I want to execute the following statement, it throws an error

SELECT ROUND(99.9, 0) 

It throws this error:

Arithmetic overflow error converting expression to data type numeric

Only 99.9 raises this error, except this, every other value produces a valid result.

What is the reason for this?

Expected result should be

100

Answer 1

The constant 99.9 is type decimal(3,1) : 3 precision digits with 1 scale digit fixed behind the decimal.

Round will return a value with the same scale and precision as the number to be rounded. 100 wont fit in a decimal(3,1) return value.

You can explicitly cast it so it will fit:

Select Round(cast(99.9 as decimal(4,1)),0) (result: 100.0) or

Select Round(cast(99.9 as decimal),0) (result: 100)