SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Converts seconds to HHH:MM:SS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 07/23/2008 :  18:48:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
7020 Posts

Posted - 08/20/2008 :  18:19:02  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 08/21/2008 :  03:42:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30242 Posts

Posted - 01/15/2009 :  03:17:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Nigeria
133 Posts

Posted - 12/02/2009 :  09:08:19  Show Profile  Visit abacusdotcom's Homepage  Send abacusdotcom a Yahoo! Message  Reply with Quote
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

USA
36941 Posts

Posted - 12/02/2009 :  11:11:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

India
22755 Posts

Posted - 12/03/2009 :  03:52:28  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

India
22755 Posts

Posted - 12/04/2009 :  01:34:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

India
22755 Posts

Posted - 12/04/2009 :  01:39:47  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote

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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000