Convert MSDB Date to Date String

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