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

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-04-28 : 17:16:16

How to Convert Seconds (i.e., 114) to hh:mm:ss format,

is there any way.. i've a column (DURATION) which is recorded in Seconds
AND USER wants to see the seconds in hh:mm:ss 00:14:28

pls.help

thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-28 : 17:49:47

This will do it.

If your DURATION is >= 86400 hours (one day), you will need to take a different approach.


select
	DURATION,
	[HH:MM:SS] = convert(varchar(20),dateadd(ss,a.DURATION,0),108)
from
	(
	-- Test Data
	select DURATION = 0	union all
	select DURATION = 111	union all
	select DURATION = 86399
	) a


Results:
DURATION    HH:MM:SS             
----------- -------------------- 
0           00:00:00
111         00:01:51
86399       23:59:59

(3 row(s) affected)

CODO ERGO SUM

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-04-28 : 20:33:27

Thank you Michael..

it worked for me..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources