MSDB - 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

ċ
udf_IntToTimeString.sql
(1k)
Andy Hughes,
Jul 20, 2012, 10:35 AM
Comments