|
|
Error Message
Server: Msg 8115, Level 16, State 5, Line 3
Arithmetic overflow error converting numeric to data type
varchar.
Causes:
This 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.
|
The error can easily be reproduced using the following script, which explicitly converts a decimal value into a varchar data type:
DECLARE @DecimalValue DECIMAL(5,3)
SET @DecimalValue = 99.999
SELECT CAST(@DecimalValue AS VARCHAR(5))
Server: Msg 8115, Level 16, State 5, Line 3
Arithmetic overflow error converting numeric to data type varchar.
Here’s another script on how to reproduce the error using an implicit conversion from a decimal data type to varchar:
DECLARE @VarCharValue VARCHAR(5)
SET @VarCharValue = 99.999
As can be seen from both scripts, the decimal value is being converted to a VARCHAR data type with a length 5, which is not long enough to hold the decimal value and the decimal. The decimal value 99.999 will need 6 characters and not just 5.
Solution / Work Around:
To avoid this problem, make sure that the length of the VARCHAR variable or column is long enough to hold the decimal or numeric value, counting the decimal point as part of the converted VARCHAR value. On both scripts, simply by increasing the length of the VARCHAR variable, the error is avoided:
DECLARE @DecimalValue DECIMAL(5,3)
SET @DecimalValue = 99.999
SELECT CAST(@DecimalValue AS VARCHAR(6))
DECLARE @VarCharValue VARCHAR(6)
SET @VarCharValue = 99.999
|