All Forums Database
KVB 124 posts Joined 09/12
19 Jan 2014
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.

KVB 124 posts Joined 09/12
19 Jan 2014

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

dnoeth 4628 posts Joined 11/04
19 Jan 2014

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

 

Dieter

KVB 124 posts Joined 09/12
19 Jan 2014

Thank you Dieter.Unfortunately,we are using TD13.

TD_Raj 50 posts Joined 05/10
20 Jan 2014

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

Saurabh...ivastava 1 post Joined 01/14
20 Jan 2014

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

Thanks and Regards,
Saurabh Srivastava

You must sign in to leave a comment.