Credits:
Author: ChillyDBA
Date: 20 Jul 2012
Description
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
Code
Function:
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
|