I'm importing a table with dates in MM/DD/YYYY format, and coming up with an error, " Invalid date supplied for test_table column 6". The file is a CSV, some sample data is below, as well as my queries.
Sample Data (note there's one null value under Expiration_date):
SyntaxEditor Code Snippet
create volatile table test_fusf (BAN number,Subscriber_No bigint,product_type varchar(1),Tax_Type integer,Tax_Auth integer,Effective_Date date format 'mm/dd/yyyy',Expiration_Date date format 'mm/dd/yyyy',CC_PC_CODE integer)ON COMMIT PRESERVE ROWS; insert into test_fusf (?,?,?,?,?,?,?,?)
The rules for importing data values into Teradata are (IMHO) very straight forward but seem to cause lots of people all sorts of trouble.
The basics are: if you're importing a character string into a date field then the Teradata dbms needs to know:
a) this value is a date field
b) the format of the incoming data - this is the one that causes most people problems.
Typically, what you need on your insert statement is something like:
field-name-in-input-data (DATE, FORMAT 'mm/dd/yyyy')
If you don't do that then the dbms still needs that information, but will pick it up from various places - usually:
- table definition - often 'yyyy-mm-dd' especially if the table is created in ANSI mode
- system default - this is often 'mm/dd/yy' (note only 2 digits for yer)
Either of the above formats will cause problems for this data.
Try that and see how you get on.