Convert MSDB Time to Time String

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