I am having a column of Tab1 table with data type as Varchar(26) , the values in this column in format as 2013-01-04-10.00.09.000000 .
I want to convert into Timestamp(6) format data ( like 2013-01-04 10:00:09.000000 )
and then cast it to Timestamp format so that i can insert into a timestamp(6) column of Tab2 table
Below is how i am trying to cast :
sel cast (cast ( ( SUBSTR(col1,1,10)||' '|| SUBSTR(col1,
12,2)||':'|| SUBSTR(col1,15,2)||':'|| SUBSTR(col1,
18,9) ) as CHAR(26)) as timestamp(6) FORMAT 'YYYY-MM-DD HH:MI:SS.S(6)' )
But it gives error as Invalid timestamp.
No need for SUBSTRINGs, but you have to specify the FORMAT correctly:
CAST(x AS TIMESTAMP(6) FORMAT 'YYYY-MM-DD-HH.MI.SS.S(6)')
I am new to TD.Some of my timestamp fileds at Timestamp(0) and some are timestamp(6).For timestamp (0) i am getting char (19) fro source and for timestamp (6),i am getting char(26).I figured i have to substring (1,26) to get ths.
I tried your suggestion and an getting 6760 invalid timesatmp.Please help.