Those pesky dates as strings

May 13, 2016

1–2 minutes

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 Smile


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 Smile

7 responses to “Those pesky dates as strings”

Hemant K Chitale

May 13, 2016 at 11:51 am

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.

Reply

Matthias Rogel

May 13, 2016 at 2:56 pm

“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                                                  471079

Kevan Gelling

May 13, 2016 at 5:00 pm

You 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

Reply
Arian

May 14, 2016 at 3:46 am

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

Reply
Sentinel

May 14, 2016 at 6:10 am

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.

Reply
ptrikha15

May 16, 2016 at 2:51 pm

Reblogged this on oratech2016 and commented:
Handling random date strings in a table.

Reply
TR Rudkin

April 25, 2017 at 10:47 pm

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/

Reply