Solved

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

Posted on 2006-11-05
11
1,274 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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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:
ScottPletcher 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

762 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

21 Experts available now in Live!

Get 1:1 Help Now