SQL>
SQL> create or replace
2 function my_to_date( p_string in varchar2 ) return date
3 as
4 type fmtArray is table of varchar2(25);
5
6 l_fmts fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy',
7 'dd/mm/yyyy',
8 'dd/mm/yyyy hh24:mi:ss' );
9 l_return date;
10 begin
11 for i in 1 .. l_fmts.count
12 loop
13 begin
14 l_return := to_date( p_string, l_fmts(i) );
15 exception
16 when others then null;
17 end;
18 EXIT when l_return is not null;
19 end loop;
20
21 if ( l_return is null )
22 then
23 l_return :=
24 new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 *
25 p_string, 'GMT', 'EST' );
26 end if;
27
28 return l_return;
29 end;
30 /
Function created.
SQL>
SQL> select my_to_date('12-02-2008') from dual;
MY_TO_DATE('12-02-20
--------------------
12-FEB-2008 00:00:00
1 row selected.
SQL>
SQL> --