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
|
