How to convert character to date in DB2

[cpeters]

I have a file that I need to import into a DB2400 table. One of the columns is a character string that is really a date in yyyymmdd format (ex. 20080520). How can I convert this into a date? Is there a built in function that will work with db2400 databases?

[docdiesel]

Hi,

in the first step you could load the data into a global temporary table, with the date as char(8) field (following named ch8dt). Then set a cursor on that table which selects a converted date from the char(8) field:

SELECT
  ...
  date( substr(ch8dt,1,4) || '-' || substr(ch8dt,5,2) || '-' || substr(ch8dt,7,2) )
  as date_converted
  ...

Then load the destination table from the defined cursor. The global temp. table will automatically be dropped when you disconnect.

Regards,

Bernd

[waynex]

Run the script below:
select cast(Left('2014 1201', 4)||'-'||substring('2 0141201',5,2)|| '-'||substring('2 0141201', 7,2) as date) from SYSIBM.SYSDUMMY 1;