I'm loading a delimited file using MLOAD and I am trying to extract the date information out of a timestamp text field. The field in the input data is in the HHMMSSMMDDYYYY format (hour, minute, second, month, day, year).
The .FIELD record in the LAYOUT file is defined as follows: .FIELD DATEFIELD_IN * VARCHAR(14);
My MLOAD commands are as follows and continue to fail on a 2666 error in my error table on this field. The ORDER_DT field is defined as a date field in the target table. INSERT INTO TARGET_TABLE VALUES ( ORDER_DT = CAST( SUBSTRING (: DATEFIELD_IN FROM 7 FOR 8) AS DATE FORMAT 'MMDDYYYY') ) ;
I'm certain that I have the input file defined properly in the LAYOUT file.