Formatting date in Multiload dd-mmm-yy

Database
Fan

Formatting date in Multiload dd-mmm-yy

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?

Thanks,

Roger

Tags (2)
3 REPLIES
Fan

Re: Formatting date in Multiload dd-mmm-yy

Additional information.

Input File layout:

.LAYOUT INPUT_LAYOUT;                                      

     .FIELD FILLER_1                     *CHAR(1);         

....

  .FIELD IN_BUS_CRT_DT            *CHAR(10) NULLIF           

         IN_BUS_CRT_DT = '          ';                       

...

Insert Statement:

INSERT INTO ZPCT03RV.ZPCV215_CLAIM_CHECK       

     (                                         

...

,BUS_RCD_CRT_DT    )

VALUES (

...

: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 

Junior Contributor

Re: Formatting date in Multiload dd-mmm-yy

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)

Dieter

Enthusiast

Re: Formatting date in Multiload dd-mmm-yy

@dnoeth I have similar issue. Input data coming as mm/dd/yy (string) while loading to teradata date field it is displaying mm/dd/yyyy. For ex: 01/01/99 is being stored as 01/01/2099.
How can we make the program to determine right centuary considering the year should not be greater than current year?