Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Converting seconds to Hours

Author  Topic 

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2008-03-12 : 12:38:30
I'm developing Telephony interaction Reports by reporting on our agents and their times on the phone, however, data shows time as seconds, so for example:

agent 1 shows total interaction time of 15794 seconds for a specific week. I just happen to know that in Hours it would be 4 hours 23 minutes 14 seconds (4:23:14). My problem is trying to convert or use a function that will return the result in parenthesis.

Can anyone assist in providing a syntax or function that I may be able to save.

thx,

John

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-12 : 12:45:20
[code]select '(' + convert(varchar(10), dateadd(second, 15794, 0), 108) + ')'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2008-04-09 : 16:49:45
Great, thanks, that works!!

However, I've just been asked to display the total seconds in hh:mm, not showing seconds. (i.e. 15794 seconds wuould be equivalent to 4 hrs, 23 min).

How can I accomplish that?

John
Go to Top of Page

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-04-09 : 17:20:55
select '(' + left(convert(varchar(10), dateadd(second, 15794, 0), 108),5) + ')'


"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking
Go to Top of Page

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2008-04-09 : 18:11:04
Thanks,
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-10 : 05:37:09

select dateadd(second, 15794, 0)

Let your reports format the time with braces

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 06:33:23
select '(' + convert(varchar(5), dateadd(second, 15794, 0), 108) + ')'


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2008-04-10 : 18:58:34
What about a scenario where 1 hour 12 minutes and 15 seconds need to be shown as 72:15 instead of 01:12:15?

What would be the syntax?

thx,

John
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 19:05:16
SELECT CAST(Col1 / 3600 AS VARCHAR(20)) + ':' + RIGHT('0' + CAST((Col1 / 60) % 60 AS VARCHAR(2)), 2)
FROM Table1


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-11 : 04:50:44
quote:
Originally posted by latingntlman

What about a scenario where 1 hour 12 minutes and 15 seconds need to be shown as 72:15 instead of 01:12:15?

What would be the syntax?

thx,

John


ASre you using any reporting tools to display data?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2008-04-11 : 09:37:06
Reporting Services, why?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-11 : 09:48:27
quote:
Originally posted by latingntlman

Reporting Services, why?


Then there must be some way to format the date over there

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -