CODEWITHSUNDEEP
oracle-database
ip696
ip696

Reputation: 7094

Oracle change column type from DATE to VARCHCAR2

I have column with name DATA_STR and type DATE. I need change column type to VARCHAR2(50). How can I do it?

Upvotes: 0

Views: 53

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59522

It was written already, however I repeat it: Do not do this!

But may you consider to use VIRTUAL COLUMN like this:

ALTER TABLE my_table ADD DATE_CHAR GENERATED ALWAYS AS (TO_CHAR(DATA_STR, 'YYYY-MM-DD HH24:MI:SS') VIRTUAL);

Upvotes: 1

Ori Marko
Ori Marko

Reputation: 58822

Although it's not recommended, you can follow the steps

  1. Add new (temp) column DATA_STR_VAR

  2. Update it using old column with to_char e.g.:

    update tableName set DATA_STR_VAR=to_char(DATA_STR, 'DD-MON-YYYY HH24:MI')

  3. Drop column DATA_STR

  4. Rename DATA_STR_VAR to DATA_STR

Upvotes: 1

Related Questions