CODEWITHSUNDEEP
sqldb2
sangram parmar
sangram parmar

Reputation: 8726

Changing datatype from DateTime to Time in DB2 gives error

Following sql is successfully executed in development database.

SQL is for changing datatype from DATETIME to TIME

ALTER TABLE SCHEDULER.MV_SCHDL_APPOINTMENT ALTER COLUMN FROM_DT SET DATA TYPE TIME;

But in deployment database it gives error

Error during Prepare 42601(-104)[IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "TIME" was found following "ROM_DT SET DATA TYPE". Expected tokens may include: "". SQLSTATE=42601

Upvotes: 1

Views: 1023

Answers (1)

sangram parmar
sangram parmar

Reputation: 8726

Hey may be it because version change of DB2

I have tried following queries to change datatype from DATETIME to TIME

ALTER TABLE SCHEDULER.MV_SCHDL_APPOINTMENT ADD COLUMN TempFromTime TIME ;

CALL SYSPROC.ADMIN_CMD ('reorg table SCHEDULER.MV_SCHDL_APPOINTMENT');

UPDATE SCHEDULER.MV_SCHDL_APPOINTMENT SET TempFromTime =CAST(FROM_DT AS TIME);
ALTER TABLE SCHEDULER.MV_SCHDL_APPOINTMENT DROP COLUMN FROM_DT;

ALTER TABLE SCHEDULER.MV_SCHDL_APPOINTMENT ADD COLUMN FROM_DT TIME ;

CALL SYSPROC.ADMIN_CMD ('reorg table SCHEDULER.MV_SCHDL_APPOINTMENT');

UPDATE SCHEDULER.MV_SCHDL_APPOINTMENT SET FROM_DT=TempFromTime ;

ALTER TABLE SCHEDULER.MV_SCHDL_APPOINTMENT DROP COLUMN TempFromTime;

CALL SYSPROC.ADMIN_CMD ('reorg table SCHEDULER.MV_SCHDL_APPOINTMENT');

Steps:

  1. Create temporary column
  2. Copy data to temporary column with updated data from desired column.
  3. Drop existing column.
  4. Add new column with design Datatype and name.
  5. Update new column data with temporary column
  6. Drop temporary column.

If you are able to rename column via query, than rename the temporary column and can skip 4,5 and 6th steps.

Following query can be used for renaming column.

ALTER TABLE SCHEDULER.MV_SCHDL_APPOINTMENT CHANGE TempFromTime FROM_DT TIME;

It complete my task.. :) but it is not so good way to do this.

I am still finding the better solution. This solution is also worked for me.

Upvotes: 0

Related Questions