Hello,

I’m working in an phone system with EPOCH time representing DateTimeConnect and DateTimeDisconnect and have those columns coded to successfully convert to a date time output.
My stumbling point is the duration column which is a value that is displayed in seconds.

I need to convert those seconds into a HH:MM:SS format in the result set. I have a CASE statement started below, but it isn’t quite getting the job done.

I reviewed several posts on this wrox, but still stumped. Any ideas?

Thanks, BPH


SELECT dateadd(s, convert(int, dateTimeConnect), '01-01-1970') AS DateTimeConnect,
    dateadd(s, convert(int, dateTimeDisconnect), '01-01-1970') AS DateTimeDisconnect,
    duration,
    Call_duration = CASE
    WHEN duration < 59 THEN CONVERT(char(8),duration,8)
    WHEN duration > 59 THEN duration/60
        WHEN duration > 3599 then duration/3600
    ELSE 'none'
    END
From CallDetailRecord

Assuming 'duration' is an integer, how about:
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Hi Jeff,

Thanks for the response. Appreciate it.

I get an error message "Incorrect syntax near 'CAST', expected 'AS'."

Line 1

Do I put that into the CASE statement, or is the case statement not needed?

BPH

Okay Jeff.
I went with convert i/o cast and it is returning results correctly.

I still have to work on the formatting. For example, if the hour, minute, or second is a single digit, the leading zero is truncated.

eg... this is 4 minutes and seconds. 0:4:9

I think I'm close.

Thanks for the help.

Sorry about the error. When I type my fingers keep getting the syntax for CAST and CONVERT confused. The CAST expressions should have been:

CAST(duration / (60 * 60) as varchar)

No CASE expression is needed.

SQL is not the best language, by a long shot, for formatting data (as you've seen). Best to do it in the client app if at all possible.



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Got it. Thanks.

You just don't need all the base 60 math, folks...

SELECT CONVERT(CHAR(8),DATEADD(ss,Duration,0),108)
FROM CallDetailRecord

--Jeff Moden

I don't think that works.

You add the duration in seconds to day zero, then convert the resultant datetime to a date format that only returns that date in HH:MM:SS format.

Suppose the call duration was more than 24 hours. The result would be the time on the second day, not the full duration.

Your technique will only work if you place restrictions on the value of 'duration', but maybe that is acceptable to the OP.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff and Jeff,

Thank you both for the insights into my issue. I have incorporated both sets code into my programming, enabling me to catch the rare occurance where an employee is on the phone greater than 23 hours, 59 mins, 59 secs. Going through the past 3 years of data, my max duration phone call is 27664 seconds, or just a bit longer than 7.5 hours.

I appreciate the help. All the best.

BPH

Jeff and BPH,

You are, of course, correct. It will not handle 24+ hours. I'm actually a bit surprised at the original request because most phone companies list duration either as whole minutes or as decimal minutes with 6 second increments (1/10ths).

That, not withstanding, given a duration in seconds, the following will handle 24 hours and much more...

DECLARE @Duration INT
SET @Duration = 361234

 SELECT STR(DATEDIFF(hh,0,DATEADD(ss,@Duration,0)),5) --Get the hours
      + RIGHT(CONVERT(CHAR(8),DATEADD(ss,@Duration,0),108) ,6) --Format the rest

--Jeff Moden