Convert seconds to HH:MM:SS - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Convert seconds to HH:MM:SS

Author

Topic

lynda
Starting Member

21 Posts

Posted - 2009-09-21 : 15:35:48

I have a column (int) that has a seconds value in it. I need to convert it to hours:minutes:seconds (HH:MM:SS) format. How would I do that?

TIA!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-21 : 15:53:20

You should not do this in T-SQL, but rather this should be done in your application as this is a presentation issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

lynda
Starting Member

21 Posts

Posted - 2009-09-21 : 16:05:46

Well it's just a one time extract of data so I need to do it in TSQL. Here is what I did:

CASE WHEN durationInSeconds/3600<10 THEN '0' ELSE '' END
+ RTRIM(durationInSeconds/3600)
+ ':' + RIGHT('0'+RTRIM((durationInSeconds % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((durationInSeconds % 3600) % 60),2)
as Duration

So I answered my own question :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-22 : 03:21:43

If it is just for display

select convert(char(8),dateadd(second,durationInSeconds,0),108)

Madhivanan

Failing to plan is Planning to fail

lynda
Starting Member

21 Posts

Posted - 2009-11-03 : 16:22:29

Excellent, Madhivanan!

Thanks for that!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-11-03 : 16:29:58

quote:
Originally posted by madhivanan

If it is just for display

select convert(char(8),dateadd(second,durationInSeconds,0),108)

Madhivanan

Failing to plan is Planning to fail

That works if the number if seconds is less than 86400 seconds (one day). If it can be more, you will need to adjust the hours part.

You may want to look at this thread.
Converts seconds to HHH:MM:SS
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107275

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-04 : 01:47:32

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by madhivanan

If it is just for display

select convert(char(8),dateadd(second,durationInSeconds,0),108)

Madhivanan

Failing to plan is Planning to fail

That works if the number if seconds is less than 86400 seconds (one day). If it can be more, you will need to adjust the hours part.

You may want to look at this thread.
Converts seconds to HHH:MM:SS
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107275

CODO ERGO SUM

Yes it is

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources