Converting a column from VARCHAR2 to DATE
I need to convert a date column created using data type VARCHAR2 into data type DATE. Is it possible?
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.
