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.
Précédent
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement