how to convert date format in Multiload

Tools

how to convert date format in Multiload

Here is a sample record from my input file (which is comma delimited)
3,840,7/3/2009,718143.47

Table structure is

NBR1 SMALLINT NOT NULL,
NBR2 SMALLINT NOT NULL,
Date1 DATE FORMAT 'YYYY-MM-DD' NOT NULL,
Amount DECIMAL(12,2) NOT NULL

I am using Mload to load this file into table
.LAYOUT SYSREC;
.FIELD NBR1 * VARCHAR(4);
.FIELD NBR2 * VARCHAR(4);
.FIELD Date1 * VARCHAR(10);
.FIELD Amount * VARCHAR(16);
. . . .
.IMPORT INFILE INFILE
FORMAT VARTEXT ','
LAYOUT SYSREC
APPLY INSERT;

Since it is comma delimited file, i have to specify FORMAT VARTEXT ',' and have to define the fields as VARCHAR.
My problem is-- How can I convert the date format in input file (mm/dd/yyyy) to load the table with date format('YYYY-MM-DD')
When I run the script as shown above, I get 2665 error with date field.

I have also tried adding (DATE, FORMAT 'YYYY-MM-DD') in the INSERT command. Still same error.

Looking for some help here.
3 REPLIES

Re: how to convert date format in Multiload

I was able to make this work by using CASE stmt and SUBSTR...
Assuming InDate is the date field on my layout

CASE WHEN Substr(InDate,3,1) = '/'
AND Substr(InDate,6,1) = '/'
-- Format is mm/dd/yyyy
THEN Substr(InDate,7,4)||'-'||Substr(InDate,1,2)||'-'||Substr(InDate,4,2)

WHEN Substr(InDate,2,1) = '/'
AND Substr(InData,5,1) = '/'
-- Format is m/dd/yyyy
THEN Substr(InDate,6,4)||'-0'||Substr(InDate,1,1)||'-'||Substr(InDate,3,2)

WHEN Substr(InDate,2,1) = '/'
AND Substr(InDate,4,1) = '/'
-- Format is m/d/yyyy
THEN Substr(InDate,5,4)||'-0'||Substr(InDate,1,1)||'-0'||Substr(InDate,3,1)

WHEN Substr(InDate,3,1) = '/'
AND Substr(InDate,5,1) = '/'
-- Format is mm/d/yyyy
THEN Substr(InDate,6,4)||'-'||Substr(InDate,1,2)||'-0'||Substr(InDate,4,1)
ELSE InDate
END (Char(10)) (Date,Format 'yyyy-mm-dd')
Enthusiast

Re: how to convert date format in Multiload

try statement as the following
DATE1 = CAST(CAST(:DATE1 AS DATE FORMAT 'DD/MM/YYYY')
AS DATE FORMAT 'YYYY-MM-DD'),

cheers
Teradata Employee

Re: how to convert date format in Multiload

Hi TD-ETL Developer,

Approach mentioned is good, you have taken care for following
d/m/yyyy
dd/m/yyyy
d/mm/yyyy
mm/dd/yyyy

also need to take care if year is coming as yy.

data seems to be extracted from DB2, If date can be formatted(in DD/MM/YYYY) during extraction this lengthy case statement could be avoided.

Thanks
Anand Agarwal
Consultant
ADC India