Why SQL Server throws Arithmetic overflow error converting int to data type numeric?
Sql ServerVariablesTypesNumericSql Server Problem Overview
I have an error being thrown by SQL Server Management Studio when running this code:
declare @percentage numeric(3,2)
set @percentage = cast(15 as numeric(3,2))
but when I change numeric declaration to
declare @percentage numeric(4,2)
set @percentage = cast(15 as numeric(4,2))
everything goes fine.
Is there a limitation for numeric data type?
Sql Server Solutions
Solution 1 - Sql Server
Numeric defines the TOTAL number of digits, and then the number after the decimal.
A numeric(3,2) can only hold up to 9.99.
Solution 2 - Sql Server
Lets see, numeric (3,2). That means you have 3 places for data and two of them are to the right of the decimal leaving only one to the left of the decimal. 15 has two places to the left of the decimal. BTW if you might have 100 as a value I'd increase that to numeric (5, 2)
Solution 3 - Sql Server
NUMERIC(3,2)
means: 3 digits in total, 2 after the decimal point. So you only have a single decimal before the decimal point.
Try NUMERIC(5,2)
- three before, two after the decimal point.
Solution 4 - Sql Server
Precision and scale are often misunderstood. In numeric(3,2) you want 3 digits overall, but 2 to the right of the decimal. If you want 15 => 15.00 so the leading 1 causes the overflow (since if you want 2 digits to the right of the decimal, there is only room on the left for one more digit). With 4,2 there is no problem because all 4 digits fit.
Solution 5 - Sql Server
Pretty old question but I thought I'd add this here for completion sake: There is a SQL Server database setting which can affect queries and throw this error.
SET NUMERIC_ROUNDABORT { ON | OFF }
Setting this to ON will cause this exact error is the result does not fit the resulting datatype.
For example, setting this to ON and running
SELECT cast(0.0 / 1 AS NUMERIC(4,2))
will throw this error.
More Info: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-numeric-roundabort-transact-sql