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
|