I am using a multiload to insert and update rows in a table. The date on the input file is formatted as dd-mmm-yy, when I insert it into the table using
:date_field (DATE, FORMAT 'DD-MMM-YY')
after the insert is complete, the date is stored as 7/25/1913
How can I get the century to be 20?
Also, how would I do this as an update statement as well?
Input File layout:
.FIELD FILLER_1 *CHAR(1);
.FIELD IN_BUS_CRT_DT *CHAR(10) NULLIF
IN_BUS_CRT_DT = ' ';
INSERT INTO ZPCT03RV.ZPCV215_CLAIM_CHECK
:IN_BUS_RCD_CRT_DT (DATE, FORMAT 'DD-MMM-YY')
Century on the Date filed is ending up as 19.
,CHK_RCD_CRT_DT = :IN_CHK_CRT_DT
This behaviour is defined by the "century break" setting, it can only be changed on a system level by your DBA using dbscontrol.
Otherwise there are two solutions:
- insert '20' using SUBSTR(IN_BUS_CRT_DT, 1, 7) || '20' || SUBSTR(IN_BUS_CRT_DT, 8)
- or add 100 years using ADD_MONTHS(IN_BUS_CRT_DT, 100*12)