Inserting into a timestamp(6) column

Database
KVB
Enthusiast

Inserting into a timestamp(6) column

Hi,

I have flat file as input and i receive a timestamp column in format 2014-01-01-10:30:30:123456

I need to load this into target timestamp(6) column.What will be better conversion to load it.

5 REPLIES
KVB
Enthusiast

Re: Inserting into a timestamp(6) column

CT DD(DT VARCHAR(26))

INS INTO DD VALUES('2014-01-01-11:10:12:123456')

INS INTO DD VALUES('2014-01-01-11:10:12:000000')

SEL CAST(SUBSTR(DT,1,19) ||'.'||SUBSTR(DT,21)  AS TIMESTAMP(6))FROM DD

I have done this.Is there any other better way of doing this

Senior Apprentice

Re: Inserting into a timestamp(6) column

If you're on TD14 you can use TO_TIMESTAMP, which is a bit more flexible than Teradata's FORMAT:

TO_TIMESTAMP(dt, 'YYYY-MM-DD-HH24:MI:SS:FF6' )
KVB
Enthusiast

Re: Inserting into a timestamp(6) column

Thank you Dieter.Unfortunately,we are using TD13.

Enthusiast

Re: Inserting into a timestamp(6) column

The answer suggested by bikky is good.

Or else you can use OS command to convert last : to . and then directly the value can be loaded into timestamp(6) column.

Raj

Re: Inserting into a timestamp(6) column

TD 14 allows data in such a format to be inserted directly in a TIMESTAMP column. You can try the same in your TD 13 database.

Saurabh