Convert HH: MM: SS

Question

I am currently working on a challenge report, but I am trapped. I need to calculate how much time the agent spent on the phone, the source data is in HH: MM: SS for the duration of the call. as in 1 hour 12 minutes 10 seconds, as applied to a few seconds. So if 2 agents received 2 calls, the time spent would be added up for that day.

Can this be changed for seconds? or can someone suggest something but better?

Answer 1

If the column type is datetime , then:

 (DATEPART(hh, @yourValue) * 60 * 60) + (DATEPART(mi, @yourValue) * 60) + DATEPART(s, @yourValue) 

Date reference

Answer 2

Time to second

Assuming this is a temporary data type, you can go for seconds like this

 DATEDIFF(second, 0, @YourTimeValue) 

And here is a simple example of aggregation (i.e. the sum)

 DECLARE @data TABLE (TimeColumn TIME) INSERT INTO @data values ('01:12:10'), ('02:15:45') SELECT SUM(DATEDIFF(SECOND, 0, TimeColumn)) FROM @data 

Result: 12475 seconds

Seconds of time

And I think to complete the image, to convert back to time format from seconds

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

or as part of an 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 leads during 03:27:55