|
|
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/Workaround:
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
|