timestamp problem

Database

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

3 REPLIES
N/A

Re: timestamp problem

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')
);

Re: timestamp problem

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.

N/A

Re: timestamp problem

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')