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?
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?
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...
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?
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'),?,?);