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