Forum FAQ: Why do I get a ‘Arithmetic overflow’ error when assigning ‘1234’ to a decimal(5,2) variable?

  • Article

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