converting seconds

[Pattycake245]

Hello,

I am working a table of categories which includes an interger column containing the totals seconds for that category. I am trying to convert the seconds to hour/minutes/seconds, but am getting strange results. It's been a long week, but, why do these two statements output such different results:

Select convert(char(8), dateadd(s, 2450000, '1900-01-01'), 8)

results in - 08:33:20

Select convert(char(8), dateadd(s, 2600000, '1900-01-01'), 8)

results in - 02:13:20

How can the first be more hours than the second?

Tim

 

[Pattycake245]

Oh man, I figured out why. There are 86400 seconds in a day, so anything after that will be a day + whatever seconds are left. So how can I get the totals hours/minutes/seconds when days are involved?

Tim

 

[mercwrought]

im going to give this a shot

select (2600021/60)/60 as hours,(2600021/60)%60 as minunts,(2600021%60) as sec

?

 

[Pattycake245]

Not very elegant, but I got it to work. If anyone needs something similar, or has a better way to show total hours by category or department or whatever, this worked for me.

select convert(char(10),shift_date,120) shift_date, category, totsecs,
cast(totaldays_to_hours+cast(left(cast(remainder_secs_to_hoursminssecs as char(8)),
charindex(':',cast(remainder_secs_to_hoursminssecs as char(8)))-1) as int) as varchar(10))+ 
right(cast(remainder_secs_to_hoursminssecs as char(8)),6) total_hours
from
(select shift_date, category, totsecs, A.totsecs/86400*24 totaldays_to_hours,
convert(varchar(8),dateadd(ss,A.totsecs%86400,'1/1/1900'),114) remainder_secs_to_hoursminssecs
from
(select shift_date, category, sum(seconds) totsecs
from interviewer_task_details where centre_id='wpg-f' and shift_date between '2005-04-01' and '2005-04-01'
group by shift_date, category) as a) as b
order by shift_date, category

Which gives these results:

shift_date category totsecs total_hours
---------- ---------- ----------- ----------------
2005-04-01 break 648672 180:11:12
2005-04-01 coach 135395 37:36:35
2005-04-01 live 4786798 1329:39:58
2005-04-01 nonbill 56352 15:39:12
2005-04-01 psmeet 157942 43:52:22
2005-04-01 train 192370 53:26:10
2005-04-01 unalloc 198859 55:14:19

Tim