Search code examples
oracleplsqlto-date

Converting to date with multiple possible masks in Oracle

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,..) ?

Solution

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