Converting to date with multiple possible masks in Oracle

Question

It so happens that I have to get a date from a varchar2 column in oracle, but it is inconsistent in formatting. Some fields might have '2009.12.31', others '2009/12/32 00:00:00'. Is there any standard construct I could use, so that I don't have to go through

begin
  to_date(date, mask1)
exception
  begin
    to_date(date,mask2)
  exception
    ..
  end
end

blocks or prior date string analysis to determine the correct mask? Something like to_date(date, mask1,mask2,..) ?

Answer 1

No, but some Oracle date formats are "forgiving" of differences e.g.

SQL> select to_date('2009.12.31','YYYY/MM/DD') from dual;

TO_DATE('20
-----------
31-DEC-2009

So that may reduce the amount of cases you need to handle. I suggest you write a function along the lines you were thinking of, so that it can be called from all the places in your code where you need to handle dates like this.

Answer 2

Any chance of making this an actual DATE field so that this madness doesn't continue?

Answer 3

You need to enumerate all the possible formats you want to support - remember that some are ambiguous (e.g. is "10-11-2009" 10 Nov or Oct 11?) so your code will have to favour one over the other.

As Tony has said, some formats will accept a variety of inputs, mainly in regards to parsing of separator characters and missing bits (e.g. 'DD-MON-YYYY HH24:MI:SS' will match '31-DEC-2009 10:30:00', '31/deC/2009 10:30', '31 dec 2009').

Apart from these simplifications, you're going to need a series of BEGIN (format1) EXCEPTION WHEN OTHERS THEN BEGIN (format2) EXCEPTION WHEN OTHERS THEN BEGIN ....