current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 2 down vote favorite

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?

share|improve this question
2  
We're going to need more information. What are the data types of Column1 and Column2? – Mark Sinkinson Jun 4 '15 at 10:51
    
Can we have some examples of the data that it is failing on? – James Anderson Jun 4 '15 at 10:55
    
@JamesAnderson Any suggestions on how to obtain that? I've got a few million rows of data, and it fails a few hundred thousand in. – Reaces Jun 4 '15 at 10:57
    
Whats the longest value in column1? – James Anderson Jun 4 '15 at 10:57
    
@JamesAnderson 8 characters long, however I don't think it's related, I have 90% of them at 8characters long (year+day+month) and 10% as 1 long (a 0) and plenty of the dates go through just fine. – Reaces Jun 4 '15 at 10:59
up vote 4 down vote accepted

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)

share|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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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