How to cast Varchar(26) Data to Timestamp(6)

General

How to cast Varchar(26) Data to Timestamp(6)

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

 from TAB1;

But it gives error as Invalid timestamp.

2 REPLIES
Junior Contributor

Re: How to cast Varchar(26) Data to Timestamp(6)

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

Re: How to cast Varchar(26) Data to Timestamp(6)

Hi Dieter,

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.

APPLY

'INSERT INTO ' ||  @TESTDB|| '.MY_TEST_DATA (

MARKET_ID        ,

TEST_ID        ,

MARKET_NO        ,

TEST_GOODS_ID ,

STORE_ID       ,

THIN_TYPE     ,

PRODUCTSREQUEST_DATE    ,

PRODUCTSRECEIVED_DATE   ,

THIN_COMPLETED_DATE   ,

NODE_TYPE_ID   ,

BILL_DATE        ,

NA_DATE   ,

TR_DATE    ,

HD_DATE       ,

SAVINGS ,

MR_AMT    ,

DAP_ID   ,

GOODS_STATUS_ID        ,

GOODS_STATUS_DATE      ,

RETURNED_STATUS_ID      ,

RETURNED_STATUS_DATE    ,

RESALE_STATUS_ID        ,

RESALE_STATUS_DATE      ,

BUY_RATE    ,

SELL_RATE)

VALUES (

                        CAST(TRIM(:MARKET_ID) AS BIGINT),

                        CAST(TRIM(:TEST_ID) AS BIGINT),

                        :MARKET_NO,

                        CAST(TRIM(:TEST_GOODS_ID) AS BIGINT),

                        :STORE_ID,

                        :THIN_TYPE,

                        CAST(SUBSTR(:PRODUCTSREQUEST_DATE,1,19) AS TIMESTAMP(0)),

                        CAST(SUBSTR(:PRODUCTSRECEIVED_DATE,1,19) AS TIMESTAMP(0)),

                         CAST(SUBSTR(:THIN_COMPLETED_DATE,1,26) AS TIMESTAMP(6)FORMAT'YYYY-MM-DD-HH.MI.SS.S(6)'),

                        :NODE_TYPE_ID,

                        CAST(SUBSTR(:BILL_DATE,1,19) AS TIMESTAMP(0)),

                        CAST(SUBSTR(:NA_DATE,1,26) AS TIMESTAMP(6)FORMAT'YYYY-MM-DD-HH.MI.SS.S(6)'),

                        CAST(SUBSTR(:TR_DATE,1,26) AS TIMESTAMP(6) FORMAT'YYYY-MM-DD-HH.MI.SS.S(6)'),

                        CAST(SUBSTR(:HD_DATE,1,19) AS TIMESTAMP(0)),

                        :SAVINGS,

                        :MR_AMT,

                        :DAP_ID,

                        :GOODS_STATUS_ID,

                        CAST(SUBSTR(:GOODS_STATUS_DATE,1,26) AS TIMESTAMP(6)FORMAT'YYYY-MM-DD-HH.MI.SS.S(6)'),

                        :RETURNED_STATUS_ID,

                        CAST(SUBSTR(:RETURNED_STATUS_DATE,1,26) AS TIMESTAMP(6)FORMAT'YYYY-MM-DD-HH.MI.SS.S(6)'),

                        :RESALE_STATUS_ID,

                        CAST(SUBSTR(:RESALE_STATUS_DATE,1,26) AS TIMESTAMP(6)FORMAT'YYYY-MM-DD-HH.MI.SS.S(6)'),

                        :BUY_RATE,

                        :SELL_RATE

                  );'

        TO OPERATOR(CONSUMER_OPERATOR() [1] )