Copying the data from another table wth diffrent timestamp

Database

Copying the data from another table wth diffrent timestamp

Hi All,

I have two tables as below

old table  (WITH DATA)

DDL for OLD table

CREATE TABLE old_table

     (

      CSTMR_ID DECIMAL(22,0),

      BACCNT_NUM VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,

      SBSCN_ID DECIMAL(22,0),

      SRVC_NUM VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      RULEID DECIMAL(10,0),

      RUNID DECIMAL(10,0),

      FIRINGTIME TIMESTAMP(6),

      OUTCOMEID DECIMAL(28,0))

PRIMARY INDEX ( CSTMR_ID );

now i want to copy all the data to another table with TIMESTAMP (0). So have created new table with timestamp(0) but when I am using

insert into new_table

select

      CSTMR_ID ,

      BACCNT_NUM ,

      SBSCN_ID ,

      SRVC_NUM ,

      RULEID ,

      RUNID ,

     cast (FIRINGTIME as timestamp(0)),

      OUTCOMEID from SUMMARY_OUTCOME_210DAYS;

I m getting the error as data time filew overflow. Can you please help me out with this?

2 REPLIES
N/A

Re: Copying the data from another table wth diffrent timestamp

Here's the solution...

Dirent CASTing of TIMESTAMP(6) to TIMESTAMP(0) won't be possible. Therefore, use CAST  to convert it into character and substring to chop unwanted part and again CAST to desired TIMESTAMP

insert into new_table

select

      CSTMR_ID ,

      BACCNT_NUM ,

      SBSCN_ID ,

      SRVC_NUM ,

      RULEID ,

      RUNID ,

     CAST(SUBSTR (CAST(FIRINGTIME AS VARCHAR(30) )  ,1,19 )  AS TIMESTAMP(0) ) AS FIRINGTIME,

      OUTCOMEID from SUMMARY_OUTCOME_210DAYS;

Re: Copying the data from another table wth diffrent timestamp

Hi Karam,

You are reallyyyy superb ........I guess Teradata is for you.....thanks man...helped a lottttt..:)))

:)))))))))))))))))))))))))))))))))))))))))