Those pesky dates as strings

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;

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
-------------------- ---------
01/01/2000           01-JAN-00
12-jan-14            12-JAN-14
20001212             12-DEC-00
Jan 14, 2016         14-JAN-16

6 rows selected.

Gotta love 12c Smile


  1. “Nothing is more frustrating that encountering a string column that is full of dates”.

    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';
    sokrates@12c > select count(*), count(first_load_time), count(to_date(first_load_time, 'yyyy-mm-dd/HH24:MI:SS')) from v$sql;
    ---------- ---------------------- -------------------------------------------------------
        471079                 471079                                                  471079
  2. 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

  3. 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’.



  4. 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.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.