Hi William,

Anyways thanks for the replies it is a tough problem but I found a solution:

I found a nice solution I will post for anyone with similar problems:

SQL> create or replace function my2date( p_string in varchar2 ) return
date
as
type array is table of varchar2(40) index by binary_integer;

l_fmts array;
l_date date default NULL;
l_str varchar2(255);
l_fromStr array;
l_toStr array;
begin
l_fmts(1) := 'DD Mon';
l_fmts(2) := 'DD-Mon-YYYY';
l_fmts(3) := 'DD Month YYYY';
l_fmts(4) := 'MM/DD-RR';
l_fmts(5) := 'MM/DD-RR "w9"';

l_fromStr(1) := '-Sept-'; l_toStr(1) := '-Sep-';
l_fromStr(2) := '-June-'; l_toStr(2) := '-Jun-';
/* ... and other things that need to be replaced ... */

for i in 1 .. l_fmts.count
loop
begin
l_date := to_date( p_string, l_fmts(i) );
exit;
exception
when others then null;
end;
end loop;

if ( l_date is NULL )
then
for j in 1 .. l_fromStr.count
loop
l_str := replace( p_string, l_fromStr(j), l_toStr(j) );
for i in 1 .. l_fmts.count
loop
begin
l_date := to_date( l_str, l_fmts(i) );
exit;
exception
when others then null;
end;
end loop;
exit when l_date is not null;
end loop;
end if;

return l_date;
end;
/

Function created.

SQL> select moc021, my2date(moc021) from styles where moc021 is not null;

or

SQL> select moc029,my2date(moc029) from styles where moc029 is not null;
MOC029 MY2DATE(M
------------------------------------------------------------ ---------
1-Aug-2000 01-AUG-00
15-Aug-2000 15-AUG-00
15-Sep-2000 15-SEP-00
15-Sep-2000 15-SEP-00
1-Oct-2000 01-OCT-00
14-Aug-2000 14-AUG-00
14-Aug-2000 14-AUG-00
1-Aug-2000 01-AUG-00
1-Aug-2000 01-AUG-00
10-Sep-2001 10-SEP-01
03 July 03-JUL-00

You might use it in the following way...

d805:sysadm> update styles set mod002=my2date(moc021);

377 rows updated.

d805:sysadm> update styles set mod003=my2date(moc029);

377 rows updated.


Cheers.