How would I make a Varchar2(8) column a date column?

I currently have a column 'COMPDATE' in TBL_APP that is set to VARCHAR2(8) that I would like to alter to make a date column.  The data is arranged as follows CCYYMMDD (20100913).
danmcd0913Asked:
Who is Participating?
 
OP_ZaharinConnect With a Mentor Commented:
- first create a new date column. i use a different name:
ALTER TABLE TBL_APP ADD COMPDATES DATE

- then update that new column to be the value of COMPDATE:
UPDATE TBL_APP SET COMPDATES = to_date(COMPDATE,'CCYYMMDD')

- then drop the COMPDATE column and rename the COMPDATES column to COMPDATE.
0
 
ajexpertCommented:
This is what I can think of...

1. Add new column in TBL_APP, say TMP_COMPDATE DATE

Alter table TBL_APP add TMP_COMPDATE DATE;

2.  Update new column with the following sql
    UPDATE TBL_APP set TMP_COMPDATE = TO_DATE('COMPDATE, 'YYYYMMDD');

3.  If above goes fine, drop column COMPDATE

4.  Rename TMP_COMPDATE to COMPDATE
0
 
danmcd0913Author Commented:
Thank you!  The only thing I had to change in the sytax was the CC in the date format it didn't accept 'CCYYMMDD' but did accept 'YYYYMMDD'.  Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.