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