The datediff function resulted in an overflow

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

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.

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.