CODEWITHSUNDEEP
sqlsql-serversql-server-2008time
GPH
GPH

Reputation: 1907

Convert HH:MM:SS

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?

Upvotes: 4

Views: 27279

Answers (3)

Eric Kramer
Eric Kramer

Reputation: 320

Two approaches...

This first is concise and works fine when there aren't fractional seconds involved (which would be truncated vs. rounded by DATEDIFF):

select DATEDIFF(second, 0, '00:12:10')

This second approach extracts the various components of the time (including fractional milliseconds):

select
      DATEPART(hh, '00:12:10.750')*60*60) + 
      DATEPART(mi, '00:12:10.750')*60 + 
      DATEPART(s, '00:12:10.750') + 
      DATEPART(millisecond, '00:12:10.750')/1000.0

If your time precision is more granular than millisecond, you can alternatively use nanosecond resolution:

select
      DATEPART(hh, '00:12:10.750')*60*60) + 
      DATEPART(mi, '00:12:10.750')*60 + 
      DATEPART(s, '00:12:10.750') + 
      DATEPART(nanosecond, '00:12:10.750')/1000000000.0

Upvotes: -1

Artur Udod
Artur Udod

Reputation: 4753

If column type is datetime then:

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

Datepart reference

Upvotes: 4

Chris Moutray
Chris Moutray

Reputation: 18399

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

Upvotes: 8

Related Questions