How to Fix the SQL Arithmetic Overflow Error Converting Varchar to Data Type Numeric

SQL Arithmetic Overflow Error Converting Varchar to Data Type Numeric

The Arithmetic Overflow Error occurs when converting a numeric or decimal value into a varchar data type, either implicitly or explicitly, and the length of the varchar variable or column is not long enough to hold the decimal or numeric value.

Example Query :

CREATE TABLE CARS(CID INT NOT NULL, CNAME VARCHAR(15),
CCOST DECIMAL(5,4) DEFAULT '15000.1234');

INSERT INTO CARS(CID,CNAME) VALUES(123,'BOLERO');
SELECT*FROM CARS;

--DROP TABLE CARS;

Error Message :

Msg 8115, Level 16, State 8, Line 1

Arithmetic overflow error converting varchar to data type numeric.

The statement has been terminated.

Solution:

Numeric data types that have fixed precision and scale decimal [(p[,s])] and numeric[(p[,s])].

When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. 

The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.

p (precision) :

The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. 

The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale) :

The no. of decimal digits that will be stored to the right of the decimal point. This number is subtracted from p to determine the maximum number of digits to the left of the decimal point. 

Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

Precision                          Storage bytes

1 - 9                                           5

10-19                                         9

20-28                                        13

29-38                                        17

Converting decimal and numeric Data :

For the decimal and numeric data types, SQL Server considers each specific combination of precision and scale as a different data type. For example, decimal(5,5) and decimal(5,0) are considered different data types.

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

Converting from decimal or numeric to float or real can cause some loss of precision. Converting from int, smallint, tinyint, float, real, money, or smallmoney to either decimal or numeric can cause overflow.

By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale. However, if the SET ARITHABORT option is ON, SQL Server raises an error when overflow occurs. Loss of only precision and scale is not sufficient to raise an error.
When converting float or real values to decimal or numeric, the decimal value will never have more than 17 decimals. Any float value < 5E-18 will always convert as 0. 

Resolved Query :

CREATE TABLE CARS(CID INT NOT NULL,CNAME VARCHAR(15),

CCOST DECIMAL(9,4) DEFAULT '15000.1234');

INSERT INTO CARS(CID,CNAME) VALUES(123,'BOLERO');

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------