All Forums Database
maheshteradata 14 posts Joined 06/12
05 Jun 2015
timestamp problem

Hi every,
I have problem with timestamp. my source data havig timestamp like '12/19/2002 12:44'
.IMPORT vartext ',' FILE= C:\XXX\TIMESTAMP.csv
.QUIET ON
.REPEAT *
USING
(
COL1 TIMESTAMP(6)
)
 
INSERT INTO XXX.TIME_TABLE VALUES
(
CAST (:COL1 AS FORMAT 'YYYY/MM/DDbHH:Mi')
);
i'm geting invalid timestamp. Can anyone help me
 

dnoeth 4628 posts Joined 11/04
05 Jun 2015

You need to define the input as a VarChar instead of a timestamp (and your format doesn't match the input):

USING
(
COL1 VARCHAR(16)
)
 
INSERT INTO XXX.TIME_TABLE VALUES
(
CAST (:COL1 AS TIMESTAMP FORMAT 'MM/DD/YYYYbHH:Mi')
);

 

Dieter

maheshteradata 14 posts Joined 06/12
05 Jun 2015

Thanks for the quick reply dnoeth.
Again i'm getting invalid timestamp.
create table xxx.time_table
(col1 timestamp);
My source file like
12/27/2007  6:29:00 PM
11/30/2007 7:01:00 am
 
My Bteq script is:
.IMPORT vartext ',' FILE= C:\XXX\TIMESTAMP.txt
.QUIET ON
.REPEAT *

USING
(
COL1 VARCHAR(60)
)
 
INSERT INTO xxx.TIME_TABLE VALUES
(
CAST (:COL1 as timestamp FORMAT 'MM/DD/YYYYbHH:Mi:ssbt')

);
 
Please help me.
 
 
 

dnoeth 4628 posts Joined 11/04
05 Jun 2015

Teradata can't use single digit month/day/hour, you might try TO_TIMESTAMP instead:
to_timestamp(upper(col1), 'mm/dd/yyyy hh:mi:ss am')

Dieter

You must sign in to leave a comment.