Convert MSDB Time to Time String

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 20 Jul 2012

Description

Converts the standard MSDB Time string format HHMMSS to 24-hour clock formatted time string HH:MM:SS.

eg 30456 would become 03:04:56

Code

Function:

DROP FUNCTION dbo.udf_IntToTimeString

GO

CREATE FUNCTION dbo.udf_IntToTimeString (@RunTime INT) RETURNS VARCHAR(8)

BEGIN

/*************************************************************

Purpose: Takes a an integer representing time

in the form HHMMSS

and convert it to a date string

(using format 113 as this is familiar and convenient)

Author: ChillyDBA

History: 20 Jul 2010 - Initial Issue

***************************************************************/

DECLARE @RunTimeText VARCHAR(12),

@OutputText VARCHAR(8)

SELECT @RunTimeText = '000000' + CAST(@RunTime AS VARCHAR(12))

SELECT @RunTimeText = RIGHT(@RunTimeText ,6)

SELECT @OutputText =

SUBSTRING(@RunTimeText, 1, 2) + ':' +

SUBSTRING(@RunTimeText, 3, 2) + ':' +

SUBSTRING(@RunTimeText, 5, 2)

RETURN @OutputText

END