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 AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
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 |
 |
|
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 |
 |
|
latingntlman
Yak Posting Veteran
96 Posts |
Posted - 2008-04-09 : 18:11:04
|
Thanks, |
 |
|
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 bracesMadhivananFailing to plan is Planning to fail |
 |
|
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" |
 |
|
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 |
 |
|
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" |
 |
|
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?MadhivananFailing to plan is Planning to fail |
 |
|
latingntlman
Yak Posting Veteran
96 Posts |
Posted - 2008-04-11 : 09:37:06
|
Reporting Services, why? |
 |
|
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 thereMadhivananFailing to plan is Planning to fail |
 |
|
|