FastLoad and Timestamp

General
Enthusiast

FastLoad and Timestamp

I am attemping to load data from an external file into the table format below, but the load always fails. I am assuming it had to do with the timestamp. Any ideas?

 

Sample file data:

"ClientID"|"SendID"|"SubscriberKey"|"EmailAddress"|"SubscriberID"|"ListID"|"EventDate"|"EventType"|"BatchID"|"TriggeredSendExternalKey"
7280191|109838|"test@gmail.com"|"test@gmail.com"|69925664|839|1/7/2018 7:04:14 AM|"Unsubscribe"|310|""

 

CREATE TABLE database.import_hold  (client_id INTEGER, send_id INTEGER, subscriber_key VARCHAR(100), email_address VARCHAR(100), subscriber_id INTEGER, list_id INTEGERevent_date TIMESTAMP(0) FORMAT 'M/D/YYYYBHH:MI:SSBT', event_type VARCHAR(10), batch_id INTEGER, triggered_send_external_key VARCHAR(4))

PRIMARY INDEX ( client_id );


Accepted Solutions
Teradata Employee

Re: FastLoad and Timestamp

That's not a valid Timestamp format for Teradata. The month, day, and hour all need to be two digits.

FastLoad can't handle this sort of data conversion, so either

  • Fix the file data (prior to loading) to have leading zeros where needed
  • FastLoad to a staging table with the column defined as a character string, and do some conversion while copying the data to the real target table
  • Use something other than FastLoad, e.g. TPT LOAD operator (TPT schema VARDATE columns allow reformatting of DateTime values without leading zeros)
1 ACCEPTED SOLUTION
1 REPLY
Teradata Employee

Re: FastLoad and Timestamp

That's not a valid Timestamp format for Teradata. The month, day, and hour all need to be two digits.

FastLoad can't handle this sort of data conversion, so either

  • Fix the file data (prior to loading) to have leading zeros where needed
  • FastLoad to a staging table with the column defined as a character string, and do some conversion while copying the data to the real target table
  • Use something other than FastLoad, e.g. TPT LOAD operator (TPT schema VARDATE columns allow reformatting of DateTime values without leading zeros)