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
Go to Top of Page

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"
Go to Top of Page

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"
Go to Top of Page

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.
Go to Top of Page

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."
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -