Importing Date issue

Database
Enthusiast

Importing Date issue

Hi there,

I'm importing a table with dates in MM/DD/YYYY format, and coming up with an error, "[2666] 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):

 

BAN,SUBSCRIBER_NO,PRODUCT_TYPE,TAX_TYPE,TAX_AUTH,EFFECTIVE_DATE,EXPIRATION_DATE,CC_PC_CODE
110603451,0,A,40,0,01/08/2015,12/31/2015,1000097
116873846,0,A,40,0,02/11/2015,09/11/2015,1000095
110616227,0,A,40,0,07/17/2015,07/17/2017,1000097
111399065,0,A,40,0,01/27/2015,12/31/3000,1000148
111409682,0,A,40,0,08/13/2015,02/13/2016,1000114

 

 

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 (?,?,?,?,?,?,?,?)

 

1 REPLY
Senior Apprentice

Re: Importing Date issue

Hi,

 

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.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com