Arithmetic overflow error converting expression to data type numeric

Question

SELECT ROUND(99.12,-1);

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

This error will come when expression range is:

95.00 to 99.99
5.00 to 9.99
995.00 to 999.99 and so on Other than that range, it works as expected.

BUT when you execute the same query in Oracle DB , it works fine and giving the expected output.

SELECT ROUND(99.12,-1) FROM DUAL;
ans: 100

so can someone explain , why it behaves like that in sqlserver2008

Thanks in advance

Tim Biegeleisen · Accepted Answer · 2016-04-26 10:56:45Z

3

When you execute SELECT ROUND(99.12,-1), the first argument is implicitly NUMERIC(4,2) and it will not allow for more than 2 digits before the decimal place.

The reason why 95.00 to 99.99 fails is that they are being rounded to 100 which cannot fit in a NUMERIC(4,2). Similar logic applies to your other edge cases.

Read this MSDN article for an eye-opening discussion.

answered Apr 26, 2016 at 10:56

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

Comments

Sean Pearce · Accepted Answer · 2016-04-26 12:00:21Z

2

SELECT ROUND(CAST(99.12 AS DECIMAL(5, 2)),-1);

answered Apr 26, 2016 at 12:00

Sean Pearce

1,1705 silver badges10 bronze badges

Collectives™ on Stack Overflow

Arithmetic overflow error converting expression to data type numeric

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related