Thanks for the question, Terry.
Asked: March 23, 2017 - 2:45 pm UTC
Last updated: April 08, 2017 - 5:22 am UTC
Version: 10.2.0.4
Viewed 1000+ times
SQL> create or replace 2 function to_date_custom(p_str varchar2) return date is 3 l_formats sys.odcivarchar2list := 4 sys.odcivarchar2list( 5 'dd/mm', 6 'yyyy-mm-dd', 7 'hh24:mi', 8 'dd-mm-yyyy-hh24'); 9 l_date date; 10 begin 11 for i in 1 .. l_formats.count loop 12 begin 13 l_date := to_date(p_str,l_formats(i)); 14 return l_date; 15 exception 16 when others then 17 if i = l_formats.count then return null; end if; 18 end; 19 end loop; 20 end; 21 / Function created. SQL> SQL> select to_date_custom('01/02') from dual; TO_DATE_C --------- 01-FEB-17 1 row selected. SQL> select to_date_custom('2017-01-01') from dual; TO_DATE_C --------- 01-JAN-17 1 row selected. SQL> select to_date_custom('01-03-2016-12') from dual; TO_DATE_C --------- 01-MAR-16 1 row selected. SQL> select to_date_custom('01/02-2016 12:34') from dual; TO_DATE_C --------- 1 row selected. SQL> select to_date_custom('01/02/2016') from dual; TO_DATE_C --------- 01-FEB-16 1 row selected.
Terry Jensen, March 24, 2017 - 1:16 pm UTC
fields terminated by ',' ( x , y , z "to_data_custom(:y)" )
Terry Jensen, March 29, 2017 - 3:37 pm UTC
Terry Jensen, April 06, 2017 - 4:19 pm UTC
Terry Jensen, April 07, 2017 - 2:12 pm UTC
Check out more PL/SQL tutorials on our LiveSQL tool.
PL/SQL reference manual from the Oracle documentation library