CAST/CONVERT problem Arithmetic overflow error

Question

SELECT works, but UPDATE does not.

Every version of CAST and CONVERT

I'm getting this error:

Arithmetic overflow error converting int to data type numeric

When I try to do this:

$sql = "UPDATE rentinc ".
       "SET curr_loss_mo = ROUND(CONVERT(numeric(3,0), DATEDIFF(DAY, missincdate, GETDATE()) / 30), 0) ".
       "WHERE months2 > 12";

I'm pretty sure there's overkill in the code above, but I was trying EVERYTHING.

I can go to SSMS and run this SELECT:

SELECT curr_loss_mo, ROUND(CONVERT(numeric(3, 0), DATEDIFF(DAY, missincdate, GETDATE()) / 30), 0) AS DateNumCalc
FROM rentinc
WHERE months2 > 12

And it works fine. And the two columns look exactly the same! The curr_loss_mo field is a numeric(3,0) and there are no values in the table that will try to update with more than three characters.

So why am I getting this error?

"Arithmetic overflow error converting int to data type numeric"

I don't quite get what the two queries have to do with each other. They have different filtering conditions, so they are not working on the same set of rows. — Gordon Linoff
You have different WHERE conditions on the two queries. Try using the same condition in MMS, it's probably a case where curr_loss_mo IS NULL which is causing your problem — Nick
The expression should fit within a numeric(3,0) for all values of missincdate between July 12, 1937 and Oct 19, 2101 (as of roughly today.) Since the error indicates that a conversion from int is the one causing a problem, I do think it has to be caused by the round() function. What is the column type for your date value? As already pointed out the filters are not the same. Do you have any blanks or zeroes being treated as Jan 1, 1900? — shawnt00
rentinc and temp_rentinc are different tables. Do they have the same schema? — AlwaysLearning

John Kiernan John Kiernan · Accepted Answer · 2019-08-30T18:45:39

As it turns out, this mystery got even deeper, but I found a workaround, so I took care of it.

I had made a temporary copy of the rentinc table called temp_rentinc. It is an EXACT COPY, as in "SELECT * INTO temp_rentinc FROM rentinc."

For reasons that totally baffle me, the UPDATE code I posted above worked with temp_rentinc, but not with rentinc! Baffled, I wrote a program to compare the structures via "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'each table name.'

The program verified that they were EXACTLY THE SAME. Sigh. Sometimes, the gremlins just get you. To make a long story short, I killed the original table, and reversed the SELECT INTO to re-create the table and the code now works fine.

This goes in the "WTF?" file. Thanks everyone for your input. -- John

CAST/CONVERT problem Arithmetic overflow error

1 Answers