How to convert seconds to hh:mm:ss

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

SQL Server Development (2000)

How to convert seconds to hh:mm:ss

Author

Topic

lw1990
Yak Posting Veteran

85 Posts

Posted - 2008-03-05 : 16:36:20

Hi,
I'm making a .txt file from a table, one field has a time duration value which datatype is int. How can I display this value in my .txt file as hh:mm:ss format. For example:
10
100
1000
Should be displayed as:
00:00:10
00:01:40
00:16:40

Thanks.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-05 : 16:59:46

declare @a table (
c1 int
)

insert into @a
select 10 union select 100 union select 1000 union select 7777

select
c1,
c1%60 as Seconds,
((c1-(c1%60))/60)%60 as Mins,
(((c1-(c1%60))/60) - (((c1-(c1%60))/60)%60))/60 as Hours,
right('00'+cast( (((c1-(c1%60))/60) - (((c1-(c1%60))/60)%60))/60 as varchar),2)
+ ':' +
right('00'+Cast((((c1-(c1%60))/60)%60) as varchar),2)
+ ':' +
right('00'+cast((c1%60) as varchar),2) as YourTimeConverted
from @a

"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

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-03-05 : 17:00:40

[code]DECLARE @a TABLE ( sec INT )
INSERT @a ( sec )
SELECT 10 UNION
SELECT 100 UNION
SELECT 1000

SELECT CONVERT( VARCHAR, DATEADD(ss, sec, 0), 8)
FROM @a[/code]

Note: Will work up to 23:59:59.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-05 : 17:07:14

Doesnt Care


declare @a table (
	c1 int
)

insert into @a 
	select 10 union select 100 union select 1000 union select 7777 union select 86399 union select 5025599
--86399 seconds is 23:59:59

select 
c1, 
c1%60 as Seconds, 
((c1-(c1%60))/60)%60 as Mins, 
(((c1-(c1%60))/60) - (((c1-(c1%60))/60)%60))/60 as Hours,
	case 
	when cast( (((c1-(c1%60))/60) - (((c1-(c1%60))/60)%60))/60 as varchar) >= 2 then cast( (((c1-(c1%60))/60) - (((c1-(c1%60))/60)%60))/60 as varchar)
	else right('00'+cast( (((c1-(c1%60))/60) - (((c1-(c1%60))/60)%60))/60 as varchar),2)
	end	+ ':' + 
	right('00'+Cast((((c1-(c1%60))/60)%60) as varchar),2) 
	+ ':' + 
	right('00'+cast((c1%60) as varchar),2) as YourTimeConverted
from @a

"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

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-03-05 : 17:09:37

>24hr:

DECLARE @a TABLE ( sec INT )
INSERT @a ( sec )
SELECT 10 UNION
SELECT 100 UNION
SELECT 1000 UNION
SELECT 100000

SELECT  CAST(DATEDIFF(dd, 0, DATEADD(ss, sec, 0)) * 24
        + CAST(LEFT(CONVERT(VARCHAR, DATEADD(ss, sec, 0), 8), 2) AS INT) AS VARCHAR)
        + RIGHT(CONVERT(VARCHAR, DATEADD(ss, sec, 0), 8), 6)
FROM    @a

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-05 : 17:13:49

Very cool

"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

lw1990
Yak Posting Veteran

85 Posts

Posted - 2008-03-05 : 18:21:34

Thank you jhocutt and jdaman.
They're working, and the short one:
CONVERT( VARCHAR, DATEADD(ss, Table1.TimeDuration1, 0), 8)
of jdaman is good for my case.

Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-06 : 08:10:43

Make it as datetime and format at your front end

select data,dateadd(minute,data,0) from
(
select 10 data union all
select 100 union all
select 1000
) as t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources