CHAR to DATE conversion

[Parnamwulan]

Hello,

I imported the file with date values .... since I dont know any possibility how to import value like '20080429' as date directly I imported the column as char and now I would like to setup trigger or counted column which will convert the char to date for me.

I wrote the trigger:

CREATE TRIGGER SCANTIME
AFTER INSERT ON xxx.ASSET_REPORT
FOR EACH ROW
UPDATE xxx.ASSET_REPORT
BEGIN
SET SCAN_DATE =
CASE
WHEN SCANTIME_DATE IS NULL
THEN 'MISSING'
WHEN SCANTIME_DATE IS NOT NULL
THEN TO_DATE (SCANTIME_DATE,'YYYYMMDD')
END;

BUT - I am getting error :
During SQL processing it returned:

SQL0171N  The data type, length or value of argument "2" of routine
"SYSIBM.TO_DATE" is incorrect.  LINE NUMBER=2.  SQLSTATE=42815

... since I wrote it following the syntax on IBM pages I dont have any idea what can be wrong there ...

Thanks a lot for help.

[docdiesel]

Hi,

please use code tags when publishing code in your posts for better readability. As for your date conversion problem:

1. Load your data to a secondary/temporary table.
2. Create a view on that table, which converts the char(8) field (e.g. named c8date) to a date value.
3. Load your destination table via cursor from the view.

The conversion part of the view should look like this:

Create View
  show_char8_as_date
AS
  select
    date(
    substr(c8date,1,4)
    || '-'
    || substr(c8date,5,2)
    || '-'
    || substr(c8date,7,2)  ) as realdate
  from ...

Regards,

Bernd