- Add a column to your table to hold the DATE data.
ALTER TABLE my_table ADD (new_col DATE);
- Set this new column's value to hold the old_column's value converted
to a DATE value.
UPDATE my_table SET new_col=TO_DATE(old_col,'MM/DD/YYYY');In this example, I used a format mask of MM/DD/YYYY assuming that my VARCHAR2 column (OLD_COL) contains the data in this format. You may have to use a different format mask.
- Drop the old column.
ALTER TABLE my_table DROP (old_col);
- Rename the new column to be the old column's name.
ALTER TABLE my_table RENAME new_col TO old_col;You may have to ensure that your application does not access the table while this type of reorganization is taking place.
This was first published in September 2005