Monday, June 6, 2011

Arithmetic overflow error converting numeric to data type numeric

Today I came across the above error. Unfortunately for me, it was nested inside a cursor inside a very long stored procedure and had to debug to find the cause.  The error seems a bit strange at first as both the datatypes mentioned are numeric. It turned out to be the precision of a decimal variable.  Let me give a practical example:


DECLARE @a decimal(18,6)

DECLARE @b decimal(18,12)

SET @a = 1336200.000000

SET @b = @a -- gives ‘Arithmetic overflow error converting numeric to data type numeric’


I think that unconsciously i assumed that 18, 12 would be bigger than 18,6. The first digit represents the number of digits before and after the decimal point, while the second digit represents the number of digits after the decimal point. Thus, (18, 12) stores less digits before the decimal in this case, which results in the above error

No comments:

Post a Comment