Arithmetic overflow error converting numeric to data type numeric in SQL Server

This error comes when you try to store an out-of-range floating point value into a numeric variable. For example, if your NUMERIC or DECIMAL variable is defined as NUMERIC(5,2) than the maximum value it can hold is 999.99, if you try to store something like 1000.00 then it will throw "Arithmetic overflow error converting numeric to data type numeric". One of the common reason of this error is the ignorance and misunderstanding of the NUMERIC data type. For example, many SQL Server DBAs and developers think that a NUMERIC(5,2) variable can hold a 7 digit floating point number where 5 digit is before decimal and 2 digits are after the decimal. This is wrong. A NUMERIC(5,2) means, the total number of digits in the value cannot exceed 5 and decimal precision is 2 digits i.e. the maximum possible value is 999.99.

Another thing SQL programmers don't know and remember about NUMERIC or DECIMAL data types in Microsoft SQL Server is that it doesn't throw this error if you specify more digits than permitted after the decimal point, instead it does rounding for example if you store 100.999 then it will store 101.00 after rounding.


Here is an SQL query to prove this points:

DECLARE @sample NUMERIC(5,2)
SET @sample = 1000.554
SELECT @sample

Output
Arithmetic overflow error converting numeric to data type numeric.


Explanation: 
This time SQL Server throws the error because we are trying to store 1000 but the maximum value a NUMERIC(5,2) can hold is 999 before the decimal point. You need to increase the width of the variable to store this number e.g. making @sample NUMERIC(6,2) will solve this error as shown below:

Arithmetic overflow error converting numeric to data type numeric in SQL Server


Here is some more SQL queries which will confirm the behavior of NUMERIC variable and its range:


DECLARE @sample NUMERIC(5,2)
SET @sample = 100.554 // no rounding because the extra digit is less than 5
SELECT @sample AS Result

Result
100.55

DECLARE @sample NUMERIC(5,2)
SET @sample = 100.555 // rounding will happen
SELECT @sample AS Result

Result
100.56


DECLARE @sample NUMERIC(5,2)
SET @sample = 100.55 // no rounding because value is under defined precision
SELECT @sample AS Result

Result
100.55


DECLARE @sample NUMERIC(5,2)
SET @sample = 100.999 // Rounding to nearest value
SELECT @sample AS Result

Result
101.00

DECLARE @sample NUMERIC(5,2)
SET @sample = 999.999 // error because after rounding value will be out-of-range for defined numeric type
SELECT @sample AS Result

Result
Arithmetic overflow error converting numeric to data type numeric.


That's all about "Arithmetic overflow error converting numeric to data type numeric in SQL Server". You can see that cause of the error is usually out-of-range value for the defined NUMERIC type. Just check the source of value and correct or increase the precision level of your column.

Always remember that NUMERIC(5,2) means total 5 digits with 2 digits after the decimal point, and maximum value it can hold is 999.99. Beware of rounding due to more additional digits after decimal point, which can also cause "Arithmetic overflow error converting numeric to data type numeric" in Microsoft SQL Server.

Related SQL Server articles you may like to explore
  • Difference between rank(), row_number(), and dense_rank() in SQL? (answer)
  • How to replace NULL with empty String in SQL Server? (tutorial)
  • Difference between Cast, Convert, and Parse method in SQL? (answer)
  • Difference between coalesce() and isNull() in Microsoft SQL Server? (answer)
  • How to remove duplicate rows from a table in SQL? (solution)
  • How to create an Identity column in SQL Server? (example)
  • How to format Date in Microsoft SQL Server? (example)
  • 5 Web sites to learn SQL online for FREE? (resource)
  • How to find the length of a String in SQL Server? (example)
  • How to convert the result of a SELECT command into a CSV String? (example)
  • The right way to check for NULL values in SQL query? (example)
  • How to split String in SQL Server 2008? (answer)
  • What is the difference between close and deallocate a cursor? (answer)
  • How to find all customers who have never ordered? (solution)
  • The right way to compare dates in SQL query? (example)
  • How to add columns into an existing table in MSSQL? (example)

Further Reading

Thanks for reading this article. If you like information given here and my explanation then please share with your friends and colleagues. 

2 comments :

Johnson Kumar said...

Nice article, Thanks so much for the clear explanation about numeric values

Javin Paul said...

Thanks Johnson, Glad you find my explanation useful.

Post a Comment