Technical Article

UDF to convert Seconds to DDD:MM:MM:SS Format

,

This function takes a number of Seconds in BIGINT format and calculations and displays that in a DDD:HH:MM:SS format.

I wrote this because I needed to work out the duration between two datetimes and required the result to be displayed in a Report (VARCHAR (12)) format from a Select statement

To Use:

SELECT dbo.csp_SecondsToDaysHoursMinutesSeconds(DATEDIFF(SS, , ))  AS Duration
FROM

Note 1: Time_2 must be > Time_1
Note 2:  DDD is the number of days to a maximum of 999

CREATE FUNCTION csp_SecondsToDaysHoursMinutesSeconds
-- Input Number of Seconds
   (@InputSeconds BIGINT )
RETURNS NVARCHAR(12) -- DDD:HH:MM:SS
AS
BEGIN

-- (c) Paul McMilllan
-- Concept On-Line Ltd
-- May 2002
-- 
-- This function takes a number of Seconds in BIGINT format and calculations and displays that in a DDD:HH:MM:SS format.
-- I wrote this because I needed to work out the duration between two datetimes and required the result to be displayed
-- in a Report (VARCHAR (12)) format from a Select statement
-- 
-- Use:
-- SELECT dbo.csp_SecondsToDaysHoursMinutesSeconds(DATEDIFF(SS, <Time_1>, <Time_2>))  AS Duration
-- FROM <Table_Name>
--
-- Note 1: Time_2 must be > Time_1
-- Note 2:  DDD is the number of days to a maximum of 999
--

   DECLARE @Days INT, @Hours INT, @Minutes INT, @Seconds INT, @FunctionOutput NVARCHAR(12)

   SELECT @Days = 0, @Hours = 0, @Minutes = 0, @Seconds = 0

   IF @InputSeconds >= 86400

      BEGIN

      SELECT @Days = @InputSeconds / 86400

      SELECT @InputSeconds = @InputSeconds - (@Days * 86400)

      END

   IF @InputSeconds >= 3600

      BEGIN

      SELECT @Hours = @InputSeconds / 3600

      SELECT @InputSeconds = @InputSeconds - (@Hours * 3600)

      END

   IF @InputSeconds >= 60

      BEGIN

      SELECT @Minutes = @InputSeconds / 60

      SELECT @InputSeconds = @InputSeconds - (@Minutes * 60)

      END

   SELECT @Seconds = @InputSeconds % 60
 
   SELECT @FunctionOutput = SUBSTRING(CONVERT(NVARCHAR, 1000 + @Days), 2, 3) + ':' + SUBSTRING(CONVERT(NVARCHAR, 100 + @Hours), 2, 2) + ':' + SUBSTRING(CONVERT(NVARCHAR, 100 + @Minutes), 2, 2) + ':' + SUBSTRING(CONVERT(NVARCHAR, 100 + @Seconds), 2, 2)

   RETURN ( @FunctionOutput )
END

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating