?
Solved

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

Posted on 2006-11-05
11
Medium Priority
?
1,338 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

839 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