CODEWITHSUNDEEP
oracle-database
johnny-b-goode
johnny-b-goode

Reputation: 3893

How to migrate column data to new data type

I need to migrate table's column to another data type with some modifications. Given: table 'USER' with column 'ORDER_TIME' (DateTime format). It's required to change column type to NUMBER, convert that time to a minutes (e.g. 8:00 AM = 480 mins.) and to store new value. As i understand, i need to create new column with required NUMBER data type, iterate over all records, do some recalculations, store new value, drop old column and rename new one to an actual name. So, basically, algorithm is:

ALTER TABLE USER ADD ORDER_TIME_MINS NUMBER(4) NULL;

iteration and recalculation

ALTER TABLE USER 
DROP COLUMN ORDER_TIME
RENAME COLUMN ORDER_TIME_MINS TO ORDER_TIME;

Can you suggest me some reading on how that iteration may looks like?

Upvotes: 2

Views: 56

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

You can try like this:

update tablename
set columnName = to_number(to_char(to_date(yourDateColumn,'hh24:mi:ss'),'sssss'))/60

And if your time is in format 08:00 PM then like

update tablename
set columnName =TO_NUMBER(TO_CHAR(TO_DATE('08:00 AM','HH:MI AM'),'SSSSS'))/60

Rest of the steps which you have shown ie, dropping and renamin are fine.

Upvotes: 2

Related Questions