Fastload - Timestamp to Date

Tools
Kde
Enthusiast

Fastload - Timestamp to Date

Hi,

Is there a way to load timestamp value from a file to a column which is defined as Date in table.

I checked the Forum , but not able to find how to implement it.
/*****************************************/

Create table Test_DT
(Start_date Date format 'YYYY-MM-DD') ;

/****************************************/

LOGON localtd/tduser,tduser ;

SET RECORD VARTEXT "|" ;

DEFINE
start_date (VARCHAR(19))
FILE=C:\Teradata_Dev\Loading_Utilities\Date_test.out;

BEGIN LOADING Test_DT
ERRORFILES Test_Dt_ET,
Test_Dt_UV;

INSERT INTO Test_DT (
start_date = :start_date
);

END LOADING;

.LOGOFF &SYSRC;

/***************************************************************/
C:\Teradata_Dev\Loading_Utilities\Date_test.out
2010-12-12 10:20:35
2010-12-13 08:06:30

/*****************************************************************/
I tried with date format option , but record went to error table and tried with timestamp ,but it throw an error saying multiple conversion is not possible.

Is it possible to implement the conversion,if so please let me know the syntax to do it.

Thanks in advance
Ks

2 REPLIES
Enthusiast

Re: Fastload - Timestamp to Date

1. Your input file as shown is not vartext - there is no length on the fromt or record delimiter.
2. If you specify the file as TEXT, you can then define the start date and time as separate fields. Just load the date field and ignore the start time in your define.
So your define then becomes:

SET RECORD TEXT ;

DEFINE
start_date (CHAR(10))
, start_time (char(9))
FILE=C:\Teradata_Dev\Loading_Utilities\Date_test.out;

You dont need to specfy the date format on the load unless your system default date format is not 'yyyy-mm-dd'.
I would specify it anyway, in case your default ever changes.

HTH and Merry Christmas

Re: Fastload - Timestamp to Date

I feel the below solution will also solve your problem.please check it once and post your comments.

INSERT INTO Test_DT values (
SUBSTRING(:Start_date FROM 1 FOR 10)
);