How to convert seconds into time/timestamp (hh:mm:ss)

Here’s another addition for your cookbook.
This scalar function is a handy one to convert seconds (int) into a string (hh:mm:ss):

sql-fn_ConvertSecondsIntoTimestamp

Here’s the code:

-- =============================================
-- Author: Clint Huijbers
-- Create date: 2014-08-02
-- Description: This scalar function converts seconds into a timestamp (varchar)
-- =============================================
CREATE FUNCTION dbo.fn_ConvertSecondsIntoTimestamp
(
@Sec INT
)
RETURNS VARCHAR(8)
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar VARCHAR(8)

SELECT
@ResultVar = CONVERT(VARCHAR(8),(r.HH+':'+r.MM+':'+r.SS),108)
FROM
(
SELECT
HH = CASE
WHEN LEN(CAST((@Sec/3600) AS VARCHAR(50))) = 1
THEN '0'+CAST((@Sec/3600) AS VARCHAR(50))
ELSE CAST((@Sec/3600) AS VARCHAR(50))
END
,MM = CASE
WHEN LEN(CAST(((@Sec%3600)/60) AS VARCHAR(50))) = 1
THEN '0'+CAST(((@Sec%3600)/60) AS VARCHAR(50))
ELSE CAST(((@Sec%3600)/60) AS VARCHAR(50))
END
,SS = CASE
WHEN LEN(CAST(((@Sec%3600)%60) AS VARCHAR(50))) = 1
THEN '0'+CAST(((@Sec%3600)%60) AS VARCHAR(50))
ELSE CAST(((@Sec%3600)%60) AS VARCHAR(50))
END
) AS r

-- Return the result of the function
RETURN @ResultVar

END
GO

How test it:

DECLARE @Sec INT = (60*60)+202
SELECT @Sec AS Sec
,[dbo].[fn_ConvertSecondsIntoTimestamp](@Sec) AS Time









Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s