Posted by You might be thinking “Nothing is more frustrating that encountering a string column that is full of dates”.
But there is something worse than that…and that is, when that string column is full of potential dates, and your job is to sift out the good data from the bad data. For example, if your table looks like this:
SQL> select * from T;
DATE_STR
--------------------
qwe
01/01/2000
31/02/2000
12-jan-14
20001212
Jan 14, 2016
6 rows selected.
Some of those values are definitely not dates, some of them definitely are dates, and some of them are might be dates. The only real way of knowing is to try convert those strings to dates, and see what happens. But we cannot just throw a TO_DATE around the column, because the moment we encounter a bad value, our query will crash. Even if all of the data was valid, because the formats of the strings are variable, we’d still get issues with that approach.
And the comes the kicker (because we get this on AskTom all the time)…
“Can we do it without creating a PLSQL function?”
This always bamboozles me…it is like saying “I need to write a book, but I’m only allowed to use a DVORAK keyboard and my tongue, with one eye closed”.
Why restrict yourself on the facilities available ?
Anyway, here is my workaround and not a stored function in sight 
SQL> with
2 function date_checker(p_str varchar2) return date is
3 l_format sys.odcivarchar2list :=
4 sys.odcivarchar2list('dd/mm/yyyy','dd-mon-yyyy','yyyymmdd','Mon DD, YYYY');
5 l_dte date;
6 begin
7 for i in 1 .. l_format.count loop
8 begin
9 l_dte := to_date(p_str,l_format(i));
10 return l_dte;
11 exception
12 when others then
13 if i = l_format.count then return null; end if;
14 end;
15 end loop;
16 end;
17 select date_str, date_checker(date_str) str_as_date
18 from t
19 /
DATE_STR STR_AS_DA
-------------------- ---------
qwe
01/01/2000 01-JAN-00
31/02/2000
12-jan-14 12-JAN-14
20001212 12-DEC-00
Jan 14, 2016 14-JAN-16
6 rows selected.
Gotta love 12c
Learning is not a spectator sport 
And when one of the rows has “06/08/2016” is it 06-Aug or 08-Jun ? You gotta love those who designed the table and built the application.
“Nothing is more frustrating that encountering a string column that is full of dates”.
true.
But: why does Oracle it internally the same ?
sokrates@12c > select data_type from all_tab_columns where table_name='V_$SQL' and column_name='FIRST_LOAD_TIME'; DATA_TYPE -------------------------------------------------------------------------------------------------------------------------------- VARCHAR2 sokrates@12c > select count(*), count(first_load_time), count(to_date(first_load_time, 'yyyy-mm-dd/HH24:MI:SS')) from v$sql; COUNT(*) COUNT(FIRST_LOAD_TIME) COUNT(TO_DATE(FIRST_LOAD_TIME,'YYYY-MM-DD/HH24:MI:SS')) ---------- ---------------------- ------------------------------------------------------- 471079 471079 471079You forgot my favourite date mask format – ‘DD/MM/RR’
It accepts all of these:
* 01/02/2003
* 01-FEB-03
* 1!FEB!3
* 1 2 3
* 010203
* 01FEB2003
And of course its reverse ‘RR/MM/DD’ is good too
Hi Kevan,
that is not dependent on the ‘DD/MM/RR’ mask. It is the rules that Oracle uses in the conversion. When it cannot match the mask directly, it will ignore the separators. Then replace values as:
‘MM’ => ‘MON’ / ‘MONTH’
‘MON => ‘MONTH’
‘MONTH’ => ‘MON’
‘YY’ => ‘YYYY’
‘RR’ => ‘RRRR’
So your examples also work with the mask ‘DD-MM-YY’.
Regards,
Arian
I use a case statement with regexp_like predicates to detect the most likely format mask. This works with pre-12c databases, and can even detect some instances of ambiguous segment orders.
Reblogged this on oratech2016 and commented:
Handling random date strings in a table.
I take a different approach to finding the format, I use regular expressions to determine the format then apply..
See http://plsql.me/does-anybody-know-what-day-this-is/