CODEWITHSUNDEEP
sql-serveroracle-database
Jay
Jay

Reputation: 49

Arithmetic overflow error converting expression to data type numeric

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:

  1. 95.00 to 99.99
  2. 5.00 to 9.99
  3. 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

Upvotes: 2

Views: 4873

Answers (2)

Sean Pearce
Sean Pearce

Reputation: 1169

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

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

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.

Upvotes: 3

Related Questions