Solved

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

Posted on 2006-11-05
11
1,294 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
0
Comment
Question by:dkilby
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17876699
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.

0
 

Author Comment

by:dkilby
ID: 17876717
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
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17876785
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
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

Author Comment

by:dkilby
ID: 17876792
No Records returned
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17876802
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

:)
0
 

Author Comment

by:dkilby
ID: 17876818
same error :(

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

Expert Comment

by:rw3admin
ID: 17877200
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
0
 
LVL 16

Expert Comment

by:Hillwaaa
ID: 17878087
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
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 17885581
Please try this:


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

Author Comment

by:dkilby
ID: 17893603
not sure why but the last answer seemed to work - thank you everyone for the help
0
 
LVL 16

Expert Comment

by:Hillwaaa
ID: 17893630
dkilby - did my answer not work?  

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

Cheers,
Hillwaaa
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question