All Forums General
satish031 1 post Joined 02/15
20 Feb 2015
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.

dnoeth 4628 posts Joined 11/04
20 Feb 2015

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

 

Dieter

newtera7 2 posts Joined 01/14
16 May 2015

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

 

                                                                                                                                                   
 
 

You must sign in to leave a comment.