| Author |
 Topic  |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
 Posted - 07/23/2008 : 18:48:22
|
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
E 12°55'05.25"
N 56°04'39.16" |
Edited by - SwePeso on 10/13/2008 07:53:32
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 08/20/2008 : 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 |
Edited by - Michael Valentine Jones on 08/20/2008 18:32:49 |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/21/2008 : 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
Sweden
29138 Posts |
Posted - 01/15/2009 : 03:17:03
|
Changed algorithm to handle larger values for secondsCREATE 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
Nigeria
130 Posts |
Posted - 12/02/2009 : 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
USA
35007 Posts |
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 12/03/2009 : 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
India
22460 Posts |
Posted - 12/04/2009 : 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
India
22460 Posts |
Posted - 12/04/2009 : 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 |
|
|
| |
Topic |
|
Converts seconds to HHH:MM:SS
