Converts 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

General SQL Server Forums

Script Library

Converts seconds to HHH:MM:SS

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 18:48:22

[code]CREATE FUNCTION dbo.fnSeconds2Time
(
@Seconds INT
)
RETURNS VARCHAR(13)
AS
BEGIN
RETURN STUFF(CONVERT(CHAR(8), DATEADD(SECOND, ABS(@Seconds), '19000101'), 8), 1, 2, CAST(@Seconds / 3600 AS VARCHAR(12)))
END[/code]

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-20 : 18:19:02

I think you need an algorithm change to handle negative numbers correctly, and you need to change the return value to varchar(13) to handle the full range of integer seconds when it is negative.

It isn't really HHH:MM:SS, since the number of hour digits can vary. I thought about this myself, and you are probably handling it the best way. It would be easy enough to add leading zeros, but then what do you do when it is more than 999 hours?

select
	a.Seconds,
	[HHH:MM:SS] = 
	dbo.fnSeconds2Time(a.Seconds),
	[HHH:MM:SS Revised] = 
	stuff(convert(char(8),dateadd(second,abs(a.Seconds),0), 8),1,2,cast(a.Seconds/3600 as varchar(12)))
from
	(
	select Seconds = 1		union all
	select Seconds = 2000000000	union all
	select Seconds = -2000000000	union all
	select Seconds = 100000		union all
	select Seconds = 1		union all
	select Seconds = 1		union all
	select Seconds = -100000	union all
	select Seconds = 0
	) a


Results:

Seconds     HHH:MM:SS    HHH:MM:SS Revised  
----------- ------------ ------------------ 
1           0:00:01      0:00:01
2000000000  555555:33:20 555555:33:20
-2000000000 -555555:26:4 -555555:33:20
100000      27:46:40     27:46:40
1           0:00:01      0:00:01
1           0:00:01      0:00:01
-100000     -27:13:20    -27:46:40
0           0:00:00      0:00:00

(8 row(s) affected)

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-21 : 03:42:59

Good catch with negative number!
I put HHH:MM:SS in the title just to make a point that this routine displays more than the normal 2 digits for hours.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 03:17:03

Changed algorithm to handle larger values for seconds

CREATE FUNCTION dbo.fnSeconds2Time
(
	@Seconds BIGINT
)
RETURNS VARCHAR(22)
AS
BEGIN
	RETURN	STUFF(CONVERT(CHAR(8), DATEADD(SECOND, ABS(@Seconds) - CAST(ABS(@Seconds) / 86400 AS BIGINT) * 86400, 0), 8), 1, 2, CAST(@Seconds / 3600 AS VARCHAR(16)))
END

E 12°55'05.63"
N 56°04'39.26"

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2009-12-02 : 09:08:19

Good Day Boss,

Kindly help with how I can make number return DD:HHH:MM:SS

Many Thanks.

I sign for fame not for shame but all the same, I sign my name.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-02 : 11:11:56

Test

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."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-03 : 03:52:28

I am not sure why I am not able to post a code in this thread

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-04 : 01:34:06

quote:
Originally posted by abacusdotcom

Good Day Boss,

Kindly help with how I can make number return DD:HHH:MM:SS

Many Thanks.

I sign for fame not for shame but all the same, I sign my name.

Concatenate the results

declare @seconds bigint
set @seconds=987234
select datepart(dayofyear,dateadd(second,@seconds,0)) ,convert(char(10),dateadd(second,@seconds,0),108)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-04 : 01:39:47

1 If you use front end application, do formation there (I forget to specify this)
2 Otherwise

Ok. Not able to use CAST function here. I let SQL Server to make use of Implicit convertion

declare @seconds bigint
set @seconds=987234
select ltrim(datepart(dayofyear,dateadd(second,@seconds,0)))+':'+convert(char(10),dateadd(second,@seconds,0),108)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources