Solved

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

Posted on 2006-11-05
11
1,281 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now