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
|
