Using CAST(date format) when inserting data to a table from import text file

Database
Enthusiast

Using CAST(date format) when inserting data to a table from import text file

I need to import data from a .txt file and insert into an existing table. One of the columns in the .txt file is a timestamp, but I would like it to be in 'MM/DD/YYYY' format.  

 

Is there a way to setup the INSERT to CAST the timestamp column to date format of 'MM/DD/YYYY' without having to change the data in the text file? Do I need to change anything in my initial CREATE TABLE? I am using TD14 SQLA.

 

CREATE TABLE test_table (client_id VARCHAR(5), event_date DATE FORMAT 'MM/DD/YYYY')

 

INSERT INTO test_table VALUES (?,CAST(? AS DATE FORMAT 'MM/DD/YYYY'));

 

Thanks!


Accepted Solutions
Junior Contributor

Re: Using CAST(date format) when inserting data to a table from import text file

When this 10/24/2017  9:49:32 AM  is actual data it will fail due to the single digit hour (Teradata always expects two digits).

TO_DATE has relaxed rules, but a different format syntax:

To_Timestamp(?, 'MM/DD/YYYY HH:MI:SS AM')

 

Instead of casting to a timestamp first you can also try

to_date(substr(?, 1, 10),'mm/dd/yyyy')

 

1 ACCEPTED SOLUTION
6 REPLIES
Teradata Employee

Re: Using CAST(date format) when inserting data to a table from import text file

The format on your insert statement needs to describe the format that is in the text file. The format in the create table statement is the default format you will see when retrieving from the table.  In neither case are you telling Teradata how to store the date - it knows what a date is and has its own format for storing it.

Enthusiast

Re: Using CAST(date format) when inserting data to a table from import text file

Thanks, that makes sense. How should I setup the INSERT when the timestamp in the flat file is formatted as 10/24/2017  9:49:32 AM?

 

INSERT INTO test_table  VALUES (?,?)

 

I have tried it a few different ways, but I still get errors. Below are a few that I tried.

-CAST(? AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYBHH:MI:SSBT') 

-TO_TIMESTAMP(?, 'MM/DD/YYYYBHH:MI:SSBT')

-cast(NULLIF(?, 'current_timestamp(0)' as timestamp(0) format ’DD-MM-YYYYBHH:MM:SSBT’ ))

Teradata Employee

Re: Using CAST(date format) when inserting data to a table from import text file

Oh! don't use the cast() function in this insert statement - that only works in SQL, when inserting from one table to another.  Just ... values(..., ? (format'MM/DD/YYYYBHH:MI:SSBT'), ...).  BTW, if you were using a load utility this format would be part of the field definition.

Junior Contributor

Re: Using CAST(date format) when inserting data to a table from import text file

When this 10/24/2017  9:49:32 AM  is actual data it will fail due to the single digit hour (Teradata always expects two digits).

TO_DATE has relaxed rules, but a different format syntax:

To_Timestamp(?, 'MM/DD/YYYY HH:MI:SS AM')

 

Instead of casting to a timestamp first you can also try

to_date(substr(?, 1, 10),'mm/dd/yyyy')

 

Enthusiast

Re: Using CAST(date format) when inserting data to a table from import text file

Thank you! 

Enthusiast

Re: Using CAST(date format) when inserting data to a table from import text file

Thank you!