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