Convert Seconds To hhh: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 hhh:mm:ss

Author

Topic

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-07 : 14:46:48

I have a function that takes seconds and converts them into hhh:mm:ss format. Note that this is not a time format, 604800 seconds is 168 hours, not 7 days. I can do it one of two ways, is one better than the other? I don't want get tripped up down the road because I didn't think of something.

DECLARE @time int
SET @time = 837171

DECLARE @Hour int

DECLARE @min int
DECLARE @secs int
DECLARE @retVal varchar(20)

SET @HOUR = @time/3600
SET @Min = (@time % 3600 )/60
SET @secs = @time %3600%60

This
SET @retval = CONVERT(varchar(10),@Hour)+':'+REPLICATE('0',2-LEN(@min))+CONVERT(varchar(10),@min)+':'+REPLICATE('0',2-LEN(@secs))+CONVERT(varchar(10),@secs)

SELECT @retval

returns the same thing as this

SET @retval = REVERSE(CAST(@hour*10000 + @min * 100 + @secs as varchar(40)))
SELECT REVERSE(LEFT(@retval,2) +':'+ SUBSTRING(@retval,3,2) +':' + SUBSTRING(@retval,5,40))

I looked for something similar here but didn't find anything. Your insights and/or soul-shattering criticisms would be appreciated.

Thanks,

Jim

marat
Yak Posting Veteran

85 Posts

Posted - 2007-08-08 : 04:46:35

declare @time int
set @time = 837171
select cast(@time/3600 as varchar(10))
+ ':' + right( '0' + cast((@time % 3600) / 60 as varchar(2)), 2)
+ ':' + right('0' + cast(@time % 3600 % 60 as varchar(2)), 2)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-08 : 06:26:41

Thanks Marat,

That looks pretty good, too. There's more than one way to skin a cat!

Jim

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-08 : 09:34:10

[code]
declare @sec int
set @sec = 837171

select
Time =
right(datediff(hh,0,ET),10)+
right(convert(varchar,ET,108),6)
from
(select ET = dateadd(ss,@sec,0)) a

Results:
Time
----------------
232:32:51

(1 row(s) affected)

[/code]

CODO ERGO SUM

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-08 : 11:37:23

It just keeps getting better. Mine didn't even handle 0 seconds correctly. That is very cool. I had tried doing it with dateadd and datediff but couldn't pull it off.

Thanks!

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources