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