Date Formating in Multiload Script

Tools
Enthusiast

Date Formating in Multiload Script

I am trying to upload a date column from as coma separated file (CSV). Date in CSV file is in dd-mm-yyyy format however Teradata expects it to be in mm/dd/yyyy format. How do I change the format ? Please help me with the syntax of the conversion or your suggestion on what can be done to tackle this problem ? Right now whatever data is uploaded is being getting moved to error table.

.logtable testdb.Logtable1;

.logon 127.0.0.1/dbc,dbc;

.BEGIN MLOAD Tables TestDB.actDate;

.LAYOUT Record_Layout;
.FIELD Script * Varchar(30);
.FIELD RECORD_DT * Varchar(10);

.DML Label CorpAction_Ins;
INSERT INTO TestDB.actDate ( Script, RECORD_DT )
VALUES(:Script, :RECORD_DT);

.IMPORT INFILE file.csv FORMAT VARTEXT ',' LAYOUT Record_Layout

.END MLOAD;

.LOGOFF;
3 REPLIES
Senior Apprentice

Re: Date Formating in Multiload Script

You have to apply a FORMAT:

.DML Label CorpAction_Ins;
INSERT INTO TestDB.actDate ( Script, RECORD_DT )
VALUES(:Script,
:RECORD_DT (date, format 'dd-mm-yyyy'));

Dieter
Enthusiast

Re: Date Formating in Multiload Script

Dieter, date is already in the dd-mm-yyyy format. I have tried the syntax you mentioned as

.DML Label CorpAction_Ins;
INSERT INTO TestDB.actDate ( Script, RECORD_DT )
VALUES(:Script,
:RECORD_DT (date, format 'mm/dd/yyyy'));

but in this case it give the error like format string contains graphics charaters.
bwb
Teradata Employee

Re: Date Formating in Multiload Script

Dieter is correct. I suspect that the confusion arises because we normally think of FORMAT as specifying the output formatting. In this case, it is specifying a pattern to match the incoming data against. Here's an example:

.LOGTABLE Holiday_Log;
.LOGON bteqdev2/bwb,
CREATE TABLE Holiday (Descrip VARCHAR(20), TheDate DATE)
UNIQUE PRIMARY INDEX (TheDate);
.BEGIN IMPORT MLOAD TABLES Holiday;
.LAYOUT Holiday_Input;
.FIELD Descrip * VARCHAR(20);
.FIELD TheDate * VARCHAR(10);
.DML LABEL Inserts;
INSERT INTO Holiday VALUES (:Descrip, :TheDate (DATE, FORMAT 'dd-mm-yyyy'));
.IMPORT INFILE Holiday_Data FORMAT VARTEXT LAYOUT Holiday_Input APPLY Inserts;
.END MLOAD;
.LOGOFF;

With a data file of:

Happy New Year|01-01-2011
Christmas|25-12-2010

If I then retrieve the rows:

SELECT TheDate, TheDate (FORMAT 'MMMMBDD,BYYYY'), Descrip FROM Holiday
ORDER BY 1;

I get (sorry about the alignment):

TheDate TheDate Descrip
-------- ------------------ --------------------
10/12/25 December 25, 2010 Christmas
11/01/01 January 01, 2011 Happy New Year

The first TheDate reflects the default yy/mm/dd date format when DATEFORM is INTEGERDATE.

This FORMAT technique will also work with TPump, Teradata Parallel Transporter (most operators), and BTEQ. However, if I recall correctly, it will not work with FastLoad (or the TPT Load Operator), because the Teradata database component of FastLoad does not allow expressions in the INSERT value list (and the cast from VARCHAR to DATE is considered an expression, I believe).

I hope this helps explain things.