CREATE A TABLE & INSERT DATA WITH A DATE FIELD

Database
Enthusiast

CREATE A TABLE & INSERT DATA WITH A DATE FIELD

Hi,

I've created a Teradata database and I have a dataset containing a DATE field (as well as other data types).

I've created a table for my data to go into in the db but when I try to import it, I get an error about the date field?

date_error.gif

Here's the code to create the table (works OK), the code to insert the data, & the select statement to view the data.

Note, after the table is created, I go to File >> Import & and then run the Import code that prompts me to browse to my data file.

I select the file and then get the error.

CREATE MULTISET TABLE A_PETER_DB.WKLY_SALES_DATE
(
ID SMALLINT NOT NULL,
WeekCommencing DATE FORMAT 'YYYY-MM-DD',	
State VARCHAR(5),	
OrderCount INT
)
PRIMARY INDEX(ID);

INSERT INTO A_PETER_DB.WKLY_SALES_DATE
VALUES(?,?,?,?);

SELECT * 
FROM A_PETER_DB.WKLY_SALES_DATE;

The format for the date in my source data is 'DD/MM/YYYY'

Where have I got this wrong?

 

Thanks

 

4 REPLIES
Teradata Employee

Re: CREATE A TABLE & INSERT DATA WITH A DATE FIELD

Hi.

 

WeekCommencing DATE FORMAT 'YYYY-MM-DD'

 The format for the date in my source data is 'DD/MM/YYYY'.

 

This should give you a clue...

 

Cheers.

 

Carlos.

Enthusiast

Re: CREATE A TABLE & INSERT DATA WITH A DATE FIELD

I've tried all sorts of combinations and the only way I could get this to work was to change the Date in my Source table to YYYY-MM-DD.

So I have tried the code with matching format with no luck.

Can you advise the correct way to do this? 

Highlighted
Junior Contributor

Re: CREATE A TABLE & INSERT DATA WITH A DATE FIELD

Do you use .Net? It always expects dates in yyyy-mm-dd.

 

The easiest way is explicitly casting in your Insert:

INSERT INTO WKLY_SALES_DATE
VALUES(?,To_Date(?, 'DD/MM/YYYY'),?,?);
Enthusiast

Re: CREATE A TABLE & INSERT DATA WITH A DATE FIELD

Hi Dieter,

I'm using ODBC, should I change to .NET?

Also, is my syntax correct?

Thanks Peter