December 30, 2010 at 9:46 am
Hey everyone,
December 30, 2010 at 9:53 am
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
December 30, 2010 at 9:59 am
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).
December 30, 2010 at 10:17 am
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.
December 30, 2010 at 10:26 am
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 toset 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.
December 30, 2010 at 10:42 am
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?
December 30, 2010 at 11:02 am
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...
December 30, 2010 at 1:52 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply