Q
Problem solve Get help with specific problems with your technologies, process and projects.

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:

  1. Add a column to your table to hold the DATE data.
       ALTER TABLE my_table ADD (new_col DATE);
    
  2. 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.
  3. Drop the old column.
       ALTER TABLE my_table DROP (old_col);
    
  4. 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.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close