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

Quite possibly, it's because whatever holds the value is limited to two digits before the decimal point and thus cannot handle 100.0 .... — marc_s
– marc_s, Commented Oct 1, 2024 at 14:18
The return type from ROUND for decimal(p, s) is again decimal(p, s). In your case 100.0 is numeric(4, 1), not numeric(3,1). — Zhorov
– Zhorov, Commented Oct 1, 2024 at 14:18
If the value is stored in a numeric(4,1) variable or column there's no error. If you use declare @num decimal(4,1)=99.9 then Select Round(@num,0) returns 100.0 which is a numeric(4,1) value - 4 digits, one fractional — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 1, 2024 at 14:22
This has been an interesting thread. I don't believe this behavior exists in any other RDBMS I've worked in (mysql, postgres, snowflake, mariadb, redshift, timescale, oracle, teradata, etc). Odd choice by microsoft. — JNevill
– JNevill, Commented Oct 1, 2024 at 16:09

Alan Schofield · Accepted Answer · 2024-10-01 16:38:25Z

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)

Alan Schofield

21.9k3 gold badges25 silver badges38 bronze badges

answered Oct 1, 2024 at 14:49

toastifer

5374 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1