DB2 For i: Convert Char YYMMDD to Date

Question

I have a CHAR_Date column containing date values in the format 'YYMMDD'.

I would like to do date arithmetic so I need to convert it into a Date data type. The problem is that the Char_Date also contains Blanks.

How do I cast the CHAR_Date to a DATE_Date column, with valid values?

Answer 1

SELECT 
 case when CHAR_Date = '' then TIMESTAMP('0001-01-01') 
                          else TIMESTAMP_FORMAT(CHAR_Date, 'YYMMDD') 
                   end 
  as DATE_Date 

 FROM TABLE_Data