SQL Server Arithmetic Overflow Error – How to Resolve Varchar to Numeric Issues

sql-server-2008-r2syntaxt-sql

I'm getting an arithmetic overflow error and I can't seem to find out the case.
I've recently had to adjust some stored procedures, to allow for dates in the future to be put in.

The following part of the ensuing data extraction now fails with arithmetic overflow errors:

SELECT  
  CONVERT(varchar(8),DATEDIFF(week, CONVERT(date,convert(varchar(8),Column1)), GETDATE()))/[Column2]  
FROM Table1;

I'm not entirely sure why this error is occurring.
There are no more than 8 characters in any given entry, and changing it to varchar(max) doesn't do anything (as you'd expect).

Removing the /[Column2] does remove the error, but I can't for the life of me figure out why.

EDIT:
CAST([Column2] as numeric(8)) does the trick.
However, select max(len([Column2])) says the longest field is 2 chars long.

[Column1] is a decimal (32,0)
[Column2] is a decimal (3,0)

So what's going on? Why is this causing a problem?

Best Answer

You get the error because essentially at the end you're dividing an 8 character long string by a numeric value which is has precision as 3 like this example below

DECLARE @num VARCHAR(8) = '12356456'
DECLARE @den DECIMAL(3,0) = 121

SELECT @num / @den

SQL Server internally tries to convert VARCHAR(8) to DECIMAL(3,0) and fails with your error.

CAST([Column2] as numeric(8)) works because now VARCHAR(8) can be converted to numeric(8).

You don't really need to convert DATEDIFF(week, CONVERT(date,convert(varchar(8),Column1)), GETDATE()) to VARCHAR(8)

Related Question