MSDB - Convert MSDB Date to Date 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 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

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