Forum FAQ: Why do I get a ‘Arithmetic overflow’ error when assigning ‘1234’ to a decimal(5,2) variable?
Summary
In SQL Server, I want to assign a string value ‘1234’ to a decimal(5,2) variable but get a ‘Arithmetic overflow’ error.
The T-SQL statements are as follows:
DECLARE @d decimal(5,2)
SET @d = '1234'
Symptom
The error message is as follows:
Arithmetic overflow error converting varchar to data type numeric.
Answer
In SQL Server, decimal and numeric are numeric data types that have fixed precision and scale.
For a decimal(5,2) variable, it can only hold three (5-2) digits before the decimal point and two digits after the decimal point. However, “1234” has four digits before the decimal point so it exceeds the precision and the error occurs.
The same issue happens if you assign an integer value 1234 to the decimal(5,2).
More Information
decimal and numeric (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms187746.aspx
Applies to
SQL Server 2000
SQL Server 2005
SQL Server 2008
SQL Server 2008 R2