I need to convert a date column created using data type VARCHAR2 into
data type DATE. Is it possible? How to do this? Thanks.
You cannot use the ALTER TABLE MODIFY command to change a VARCHAR2
column to a DATE column. However, you can do a multi-step process to
get your data converted. Follow these steps:
- 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.