Parsing date out of timestamp text field in MLOAD

Tools & Utilities
Enthusiast

Parsing date out of timestamp text field in MLOAD

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.

Any idea why it doesn't like my date?
1 REPLY
Enthusiast

Re: Parsing date out of timestamp text field in MLOAD

Got it figured out.

The date in the data was actually in the DDMMYYYY format and not MMDDYYYY.

Works fine now.