Converting seconds to HH:MM:SS
Since a lot of people are asking for a good way to convert seconds to HH:MM:SS format, I created a small function to do this. After posting my answer to the tek-tips forum, I received an even better solution. So here it is:
CREATE FUNCTION SecTimeDay (@sec integer)
RETURNS VARCHAR(19)
AS
BEGIN
DECLARE @DayTime VARCHAR(19)
/* Calculate # of days and display if necessary */
SELECT @DayTime =
CASE WHEN @Sec >= 86400
THEN CONVERT(VARCHAR(5), @Sec/86400) + ' days '
ELSE ''
END
/* Add HH:MM:SS to number of days (or ') for output */
+ CONVERT(VARCHAR(8), DATEADD(Second, @Sec, 0), 108)
RETURN @DayTime
END
3 comments:
This is really helpful
This really help me.
Thanks
Here is how I incorporated this function into another stored procedure which allows cyclical
calls to an external stored procedure at a predetermined interval by using the WAITFOR DELAY '0005:30' feature
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Calling Convention...
-- usp_TimingLoop 'usp_LittleBankTest', 60, .50
-- This stored procedure calls any given additional stored procedure at an interval
-- determined by the input parameter for Hourly Frequency (60x per hour means the
-- WAITFOR DELAY must be converted to: 00:01:00) or a 1 minute interval cycle...
-- Copyright 2008-2009 John Bevilaqua jfbevilaqua@gmail.com http://www.wordhyperlinks.com
-- This Copyright notice must remain intact for proper author attribution
ALTER PROC [dbo].[usp_TimingLoop] (@SprocName varchar(100), @HourlyFrequency int, @HourDuration float)
as
SET NOCOUNT ON
DECLARE @SecondsDuration int
DECLARE @IntervalSeconds decimal(10,2)
SET @IntervalSeconds = CAST( ROUND(1.00*(3600/@HourlyFrequency),0) AS Int)
SELECT @IntervalSeconds as IntervalSeconds
SET @SecondsDuration = 1.00*(@HourDuration * 3600)
SELECT @SecondsDuration as TotalRunSeconds
BEGIN
DECLARE @WaitForDelay VARCHAR(19)
/* Calculate # of days and display as necessary */
SELECT @WaitForDelay =
CASE WHEN @IntervalSeconds >= 86400
THEN CONVERT(VARCHAR(5), @IntervalSeconds/86400) + ' day(s) '
ELSE ''
END
+ CONVERT(VARCHAR(8), DATEADD(Second, @IntervalSeconds, 0), 108)
SELECT @WaitForDelay as WaitForDelay
END
DECLARE @TargetEndDtTm datetime;
SET @TargetEndDtTm = DATEADD(second,@SecondsDuration,GetDate())
SELECT DATEADD(second,@SecondsDuration,@TargetEndDtTm); --2007-01-01 01:02:00.110
SELECT @TargetEndDtTm AS EndingDateTime
SELECT GetDate() AS CurrGetDate
DECLARE @intFlag INT
SET @intFlag = 0
WHILE GetDate() <= @TargetEndDtTm
BEGIN
IF GetDate() > @TargetEndDtTm -- '2009-01-04 12:38:40.000'
BEGIN
SELECT @intFlag
SELECT @TargetEndDtTm, GetDate() as CurrDtTime
BREAK;
END
IF GetDate() <= @TargetEndDtTm -- '2009-01-04 12:38:40.000'
BEGIN
WAITFOR DELAY @WaitForDelay -- '00:02:00'
EXEC dbo.usp_LittleBankTest
SELECT @TargetEndDtTm AS EndingDateTime
SELECT GetDate() AS CurrGetDate
SET @intFlag = @intFlag + 1
SELECT @intFlag AS RunCount
END
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
Post a Comment