We help IT Professionals succeed at work.

ms sql + Difference of two datetime columns caused overflow at runtime

dkilby
dkilby asked
on
Medium Priority
1,514 Views
Last Modified: 2009-07-29
I am trying to get the difference between 2 dates, starttime, endtime but get the error - Difference of two datetime columns caused overflow at runtime, i only get the error when i try to get the average of all the difference, i broke  down the list to try and see what was wrong I think it could be there are some instances where endtime is greater than startime create a negative number, but apart from that i can not see anything.

If i run the query as

select DATEDIFF(millisecond, start_time, end_time) AS 'worktime'
FROM T_WORK_LIST

it pulls a complete list, but if i try doing anything else i get the error

select AVG(DATEDIFF(millisecond, start_time, end_time)) AS 'worktime'
FROM T_WORK_LIST

i even tried selecting only datediff > 1 but got the error

select DATEDIFF(millisecond, start_time, end_time) AS 'worktime'
FROM T_WORK_LIST
WHERE DATEDIFF(millisecond, start_time, end_time) > 1

Greatest value in the list = 3625086
Lowest value in the list = -16
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
from Bol
DATEDIFF produces an error if the result is out of range for integer values. For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number is 68 years.

Author

Commented:
The maximum value of any given value in the list is 3625 = or just over 1hr, the sum of the whole list = around 43 hrs, so not sure why i am getting the error
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
dkilby,

try this to see whether there exists any records

select DATEDIFF(dd, start_time, end_time) AS 'worktime'
FROM T_WORK_LIST
WHERE DATEDIFF(dd, start_time, end_time) > 1

Author

Commented:
No Records returned
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
just guessing ,can u try this

select AVG(CAST(DATEDIFF(millisecond, start_time, end_time) as numeric(10,2))) AS 'worktime'
FROM T_WORK_LIST

:)

Author

Commented:
same error :(

Server: Msg 535, Level 16, State 1, Line 8
Difference of two datetime columns caused overflow at runtime

Commented:
its very odd that you are getting this error if your min and max values are
Greatest value in the list = 3625086
Lowest value in the list = -16
lets do this and post your results (if you get any without an error back here)

Select Min(WorkTime), Max(WorkTime), Avg(WorkTime) from
(
select DATEDIFF(millisecond, start_time, end_time) AS 'worktime'
FROM T_WORK_LIST ) as A

Commented:
Hey dkilby,

I think that the error is being caused by the total value of the datediff that is added together, before the divide is performed in the average function.

To avoid this you could try:

select AVG(cast(DATEDIFF(millisecond, start_time, end_time) as bigint)) AS 'worktime'
FROM T_WORK_LIST

This should force the average to sum all the datediff values as bigints before dividing by the number of rows.  If this still errors, then I would suggest doing the calculation using a larger datediff datepart (i.e. seconds or minutes) then multiplying out the result to milliseconds if required (and if the resulting average value is not too large).

Cheers,
Hillwaaa
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Please try this:


SELECT AVG(CAST(DATEDIFF(millisecond, start_time, end_time) AS DECIMAL(19, 1))) AS 'worktime'
FROM T_WORK_LIST

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
not sure why but the last answer seemed to work - thank you everyone for the help

Commented:
dkilby - did my answer not work?  

just wondering if the bigint caused a problem where decimal worked?

Cheers,
Hillwaaa
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.