Convert HH:MM:SS

Question

I'm currently working on a call report however i've hit a snag. I need to calculate how long an agent has spent on the phone, the source data is in HH:MM:SS for the call duration. as in 1hr 12mins 10 seconds as appose to a number of seconds. So if 2 agents had taken 2 calls the time spent would sum up for that day.

Is it possible to change this into seconds? or can anyone suggest something a but better?

Answer 1

Time to Seconds

Assuming it's a time datatype then you can change to seconds like this

DATEDIFF(second, 0, @YourTimeValue)

And here's a simple aggregation example (ie sum)

DECLARE @data TABLE (TimeColumn TIME)
INSERT INTO @data values ('01:12:10'), ('02:15:45')

SELECT SUM(DATEDIFF(SECOND, 0, TimeColumn)) FROM @data

Which results in 12475 seconds

Seconds to Time

And I guess to complete the picture to convert back to time format from seconds

SELECT CAST(DATEADD(SECOND, @TotalSecondsValue, 0) AS TIME)

or as part of the aggregation example

DECLARE @data TABLE (TimeColumn TIME)
INSERT INTO @data VALUES ('01:12:10'), ('02:15:45')

SELECT CAST(DATEADD(SECOND, SUM(DATEDIFF(SECOND, 0, TimeColumn)), 0) AS TIME) FROM @data

Which results in a time of 03:27:55