Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 16th, 2006, 12:05 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default Convert seconds to HH:MM:SS

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


 
Old October 16th, 2006, 01:00 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Assuming 'duration' is an integer, how about:
Code:
SELECT CAST(varchar, duration / (60 * 60)) + ':' +
       CAST(varchar, (duration % (60 * 60)) / 60) + ':' +
       CAST(varchar, (duration % (60 * 60 * 60)) % 60) as Call_Duration
  FROM CallDetailRecord
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old October 16th, 2006, 01:10 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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



 
Old October 16th, 2006, 01:46 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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.


 
Old October 16th, 2006, 02:10 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old October 16th, 2006, 02:29 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Got it. Thanks.

 
Old October 16th, 2006, 10:57 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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

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

--Jeff Moden
 
Old October 17th, 2006, 05:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old October 17th, 2006, 09:38 AM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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

 
Old October 18th, 2006, 02:04 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
converting HH:MM to HH.MM nasirmunir PHP How-To 5 July 11th, 2008 01:33 PM
Convert time in seconds to hh:mm:ss 41509331 C++ Programming 0 March 18th, 2008 04:43 AM
Converting number to hh:mm dlyles Beginning VB 6 8 September 19th, 2006 12:27 PM
Convert Seconds to HH:MM:SS jpillonel Crystal Reports 0 December 17th, 2003 09:20 AM
validation of [HH]:mm beastwood_uk Excel VBA 3 August 10th, 2003 08:37 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.