-3

I am trying to get the total expended time by a call operator in 'HH:MM:SS' format, by calculating the difference between the start time and end time using the following query.

SELECT  OPERATOR_ID ID,OPERATOR_NAME NAME, 
        convert(varchar(8),dateadd(second, SUM(DATEDIFF(second, 
        start_time,END_TIME)), 0),108) as 'Totaltime' 
FROM    INBOUND_CALL_TRANSFER,
        OPERATOR_MST OM
where   START_TIME between isnull(@from,START_TIME) and isnull(@to,START_TIME)
group by operator_id

This outputs the difference as hh:mm:ss by calculating the difference in seconds and converting the result to a varchar.

Msg 535, Level 16, State 0, Line 7 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

When the difference between start time and end time is not too big then I get the proper result but when the difference is big then the above error occurs.

I am using SQL Server 2014

  • 2
    DATEDIFF function returns results in an integer value type, and it cannot contain larger value than : 2,147,483,64. That is 68 years in seconds, do you really need that? – S4V1N Jul 4 '17 at 8:12
  • 1
    Putting @S4V1N's point differently, when the difference in seconds between two time points is so large that it causes overflow, formatting the result as hh:mm:ss makes no sense. – Andriy M Jul 4 '17 at 8:15
  • @AndriyM i am geting like this 01:20:36(HH:MM:SS)..i thing no issue with this – Gulrez Khan Jul 4 '17 at 11:26
  • 1
    01:20:36 is 4836 seconds. That's too far from the nearest value that would cause an overflow. Even 23:59:59, the maximum that you can get using your method, is still too far. If datediff gives you a result somewhere in the millions or more, then converting it to hh:mm:ss using the method in your example makes no sense (at least based on the amount of information you've given us). – Andriy M Jul 4 '17 at 11:51
4

The error is caused when the DATEDIFF returns a value larger than 2,147,483,64 because the function returns an integer.

This means that as soon as your difference in seconds between start and end time exceeds the maximum value of an integer data type you will be getting that error.

If you were on SQL Server 2016 or Azure SQL Database you could use DATEDIFF_BIG.

However, as noted in the comments, If the difference is over 68 years one has to wonder if it makes sense to express the difference in seconds or even express it in an hh:mm:ss format.

As you noted in a comment, you had a row with 1900-01-01 which would obviously result in an overflow.

  • Firstly, no need to find difference of 68 years..i accepted..someone inserted the record end_date like '1900-01-01'.solved – Gulrez Khan Jul 4 '17 at 11:45
1

With the cause found, you could change the formula to,

DATEDIFF(second, CONVERT(time, START_TIME), CONVERT(time, END_TIME))

This reduces the start and end time data to a time-of-day value. However, if the time crosses midnight then you'll get a large negative value of DATEDIFF. e.g. start 23:59, end 00:00, datediff is -86340 I think (1 minute minus 24 hours). So datetime of 1900-01-01 is still a problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.