If you have a sound idea of all the possible date formats it might be easier to use brute force:
create or replace function clean_date
( p_date_str in varchar2)
return date
is
l_dt_fmt_nt sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll
('DD-MON-YYYY', 'DD-MON-YY', 'DD-MM-YYYY', 'MM-DD-YYYY', 'YYYY-MM-DD'
, 'DD/MM/YYYY', 'MM/DD/YYYY', 'YYYY/MM/DD', 'DD/MM/YY', 'MM/DD/YY');
return_value date;
begin
for idx in l_dt_fmt_nt.first()..l_dt_fmt_nt.last()
loop
begin
return_value := to_date(p_date_str, l_dt_fmt_nt(idx));
exit;
exception
when others then null;
end;
end loop;
if return_value is null then
raise no_data_found;
end if;
return return_value;
exception
when no_data_found then
raise_application_error(-20000, p_date_str|| ' is unknown date format');
end clean_date;
/
Be aware that modern versions of Oracle are quite forgiving with date conversion. This function handled dates in formats which aren't in the list, with some interesting consequences:
SQL> select clean_date('20160817') from dual;
CLEAN_DAT
---------
17-AUG-16
SQL> select clean_date('160817') from dual;
CLEAN_DAT
---------
16-AUG-17
SQL>
Which demonstrates the limits of automated data cleansing in the face of lax data integrity rules. The wages of sin is corrupted data.
@AlexPoole raises the matter of using the 'RR' format. This element of the date mask was introduced as a Y2K kludge. It's rather depressing that we're still discussing it almost two decades into the new Millennium.
Anyway, the issue is this. If we cast this string '161225' to a date what century does it have? Well, 'yymmdd' will give 2016-12-15. Fair enough, but what about '991225'? How likely is that the date we really want is 2099-12-15? This is where the 'RR' format comes into play. Basically it defaults the century: numbers 00-49 default to 20, 50-99 default to 19. This window was determined by the Y2K issue: in 2000 it was more likely that '98 referred to the recent past than the near future, and similar logic applied to '02. Hence the halfway point of 1950. Note this is a fixed point not a sliding window. As we move further from the year 2000 the less useful that pivot point becomes. Find out more.
Anyway, the key point is that 'RRRR' does not play nicely with other date formats: to_date('501212', 'rrrrmmdd') hurlsora-01843: not a valid month. So, use'RR'and test for it before using'YYYY'`. So my revised function (with some tidying up) looks like this:
create or replace function clean_date
( p_date_str in varchar2)
return date
is
l_dt_fmt_nt sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll
('DD-MM-RR', 'MM-DD-RR', 'RR-MM-DD', 'RR-DD-MM'
, 'DD-MM-YYYY', 'MM-DD-YYYY', 'YYYY-MM-DD', 'YYYY-DD-MM');
return_value date;
begin
for idx in l_dt_fmt_nt.first()..l_dt_fmt_nt.last()
loop
begin
return_value := to_date(p_date_str, l_dt_fmt_nt(idx));
exit;
exception
when others then null;
end;
end loop;
if return_value is null then
raise no_data_found;
end if;
return return_value;
exception
when no_data_found then
raise_application_error(-20000, p_date_str|| ' is unknown date format');
end clean_date;
/
The key point remains: there's a limit to how smart we can make this function when it comes to interpreting dates, so make sure you lead with the best fit. If you think most of your date strings fit day-month-year put that first; you will still get some wrong casts but less that if you lead with year-month-day.
