2

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?

  • Why are you storing dates in a decimal column? And why are they converted to varchar? – ypercubeᵀᴹ Jun 4 '15 at 11:20
  • @ypercube Because... Legacy. I didn't do any of the design on this part, and it's almost a decade old. All I did was edit a where clause in the very start to allow for dates in the future, and this particular part started throwing errors. I guess your suggestion is to rewrite the entire thing? – Reaces Jun 4 '15 at 11:21
4

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)

| improve this answer | |
  • I'm trying to find an example where the datediff action actually comes up with a digit larger length than 3. – Reaces Jun 4 '15 at 11:50
  • This will select datediff(week,0,GETDATE()) or if you use a valid date 19900101 – ughai Jun 4 '15 at 11:51
  • 1
    Using your suggestion I found the following two entries in the dates column: 19741211 and 19741211. Apparently the new (future) dates they added in the app also include visits 41 years in the past. – Reaces Jun 4 '15 at 11:56
  • It might be due to cut off for YY format – ughai Jun 4 '15 at 12:04
  • 2
    I went to the business users of the app, and asked them to show me that specific visit. And indeed one of them had put in 1974. The reason being she wanted to visit the customer in the future, hadn't decided when, and figured she could "save" the filled in visit by putting in a ridiculous date so that the customer did not yet get an email. – Reaces Jun 4 '15 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.