Oracle Database Discussions

Hi,

one DBA created web_type column as varchar2(35) instead of date datatype from dev to production. Now users have inserted data in to those columns where web_update is varchar2(35) datatype. The below code worked if the date date is inserted in varchar2(35).

alter table eligible add temp_date date;
update eligible set temp_date=to_date(WEB_UPDATE);
update eligible set WEB_UPDATE=null;
alter table eligible modify WEB_UPDATE date;
update eligible set WEB_UPDATE=temp_date;
alter table eligible drop column temp_date;


If data is inserted into web_update as Ex: 29-JUN-2011, the below conversion is failing - to_date(LAST_UPDATE).


SQL> update doc_meta set temp_date=to_date(WEB_UPDATE);
update doc_meta set temp_date=to_date(WEB_UPDATE)
*
ERROR at line 1:
ORA-01843: not a valid month

Please let me know how to convert varchar2(35) datatype to date datatype. There are lot of rows.

Thanks