Thursday, May 27, 2010

Convert CYYMMDD format to datetime - SQL

Convert CYYMMDD to DateTime,Convert CYYMMDD to DateTime Format,SQL,SQL Query,AS400 datetime
In the date format CYYMMDD(AS400 date format), C is Century.

If C is 0 the year is 19XX, and it is 20XX if C is 1(Where XX can be any
two digit year).

YYMMDD is Year Month and Day(All two digit).

In CYYMMDD format, today's date (May 27, 2010) would be 1100527.

It is '0' for 19 and '1' for 20 to make our comparisons easy.

But the '0' will not present in the table data as the leading zeroes of numeric will be truncated.

The following query converts the CYYMMDD format to datetime format:

SELECT dbo.TBL.ACTDT  AS ActualDate, CAST(CASE WHEN SUBSTRING(RIGHT('00' + CAST(dbo.TBL.ACTDT AS VARCHAR), 7), 1, 1) = '0' THEN '19' ELSE '20' END + SUBSTRING(RIGHT('00' + CAST(dbo.TBL.ACTDT AS VARCHAR), 7), 2, 2) + SUBSTRING(RIGHT('00' + CAST(dbo.TBL.ACTDT AS VARCHAR), 7), 4, 4) AS DATETIME) AS [Converted Date] FROM TBL


Query Result:

Convert CYYMMDD Format to datetime format

5 comments:

  1. Wonderful i kept searching for that

    ReplyDelete
  2. Brillant, just the one i am looking for

    ReplyDelete
  3. Simply add 19000000 to a CYYMMDD date to get YYYYMMDD. Example 1991231 + 19000000 = 20991231

    ReplyDelete
  4. Create a function - and using the 19000000 trick

    DECLARE MYDATE CHAR ( 8 ) ;
    SET MYDATE = CAST ( ( P_INPUT + 19000000 ) AS CHAR ( 8 ) ) ;
    RETURN DATE ( LEFT ( MYDATE , 4 ) CONCAT '-' CONCAT SUBSTR ( MYDATE , 5 , 2 ) CONCAT '-' CONCAT SUBSTR ( MYDATE , 7 , 2 ) ) ;

    ReplyDelete