Author:    ChillyDBA

Date:        20 Jul 2012

Converts the standard MSDB date integer in the format YYYYMMDD to a date string of the format 'DD MMM YYYY' 

eg 20120720 becomes 20 Jul 2012

DROP FUNCTION dbo.udf_IntToDateString GO CREATE FUNCTION dbo.udf_IntToDateString (@RunDate INT) RETURNS VARCHAR(11) BEGIN /************************************************************* Purpose:   Takes an integer representing date           in the form  YYYYMMDD           and converts it to a date string           (using format 113 as this is familiar and convenient)            Author:        ChillyDBA History:   20 Jul 2012 - Initial Issue ***************************************************************/    DECLARE @RunDateText    VARCHAR(16),            @OutputText     VARCHAR(11)    SELECT  @RunDateText = '00000000' + CAST(@RunDate AS VARCHAR(16))    SELECT  @RunDateText = RIGHT(@RunDateText ,8)    SELECT @OutputText =                    SUBSTRING(@RunDateText, 1, 4) + '-' +                      SUBSTRING(@RunDateText, 5, 2) + '-' +                      SUBSTRING(@RunDateText, 7, 2) + ' '    -- convert to a friendly format - language independant and not suffering from DDMM MMDD confusion    RETURN CONVERT(VARCHAR(11), CAST(@OutputText AS DATETIME), 113) END