Arithmetic overflow error converting int to data type numeric

Question

Post reply

Arithmetic overflow error converting int to data type numeric

Richard.Westhaver

Mr or Mrs. 500

Points: 542
More actions
December 30, 2010 at 9:46 am

#229996

Hey everyone,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Richard.Westhaver Mr or Mrs. 500 Points: 542 More actions · Answer 1

Hey everyone,

Sorry about te first response.

Here's something I haven't seen. I'm running a Stored procedure that's pulling in data Year to Date however, I keep getting the arithmetic overflow error. SO I added in a startdate and enddate parameter and decided to run it Month over Month.

No issue for the most part until I run a month with 31 days, then the error occurs. If I run that month from the 1st to the 30th instead of the 31st for those months with 31 days, no issue.

Anyone ever seen this before, and anyone have any ideas on how this could get resolved?

Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 2

Without seeing the actual code, it's unlikely to find an easy answer.

The error message usually will give you the line number where the error occured or you can simply double click it and it will bring you to the line in question if you run the code all by itself (not starting the sproc).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

dva2007 SSCertifiable Points: 7707 More actions · Answer 3

You need to check the dateformat in the date time field you are using. If it is ymd then set on top before running t-sql statement to

set dateformat ymd

if it is different then set that way and then run it.

it could be dmy, in that case

set dateformat dmy

it may be that some of your date field is wrong in your dataset. Some times it happens that date is before 1900 which is not recognised by SQL Server so you should group by that field and order by without paramter to find out dodgy date.

LutzM SSC Guru Points: 107049 More actions · Answer 4

dva2007 (12/30/2010)
You need to check the dateformat in the date time field you are using. If it is ymd then set on top before running t-sql statement to
set dateformat ymd
if it is different then set that way and then run it.
it could be dmy, in that case
set dateformat dmy
it may be that some of your date field is wrong in your dataset. Some times it happens that date is before 1900 which is not recognised by SQL Server so you should group by that field and order by without paramter to find out dodgy date.

If the error would start with the 13th day of a month, I'd agree. But I don't think a deateformat change would make any difference here. Therefore I asked for the code snippet.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Richard.Westhaver Mr or Mrs. 500 Points: 542 More actions · Answer 5

I think I found the issue. the format of the date field is YYYY MM DD HH:MM:SS:MMM. and the startdate/enddate parameters are smalldatetime so I think I need to run a convert on the date field in order for it to not come up with the overflow. The only thing is, why was it doing it only on the 31st of every month?

LutzM SSC Guru Points: 107049 More actions · Answer 6

Smalldatetime and Datetime get along very well...

Even if your data are in a character format (*yikes*), the implicit conversion should take care of it.

I don't think this will explain the 30/31 error...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Richard.Westhaver Mr or Mrs. 500 Points: 542 More actions · Answer 7

Got it. the Stored Procedure is I have is using nested queries to create fields based on specific codes.

One of the nested queries is looking at total number of hours based on the code "Other". The Total Number of hours is being pulled like this:

CAST(sum(t.numMinutes) as DECIMAL(9,2))/60 AS NumHours.

This is where the issue is.

What I ended up doing is rewriting this piece like this:

sum(numminutes)/60 AS NumHours, getting rid of the cast on it.

Worked like a charm.