Oracle Varchar2 column with multiple date formats

Question

I have a table (my table ) with a varchar2 column (my_column) , this column inculdes data that may be dates, in almost every known date format. like dd/mm/yyyy mm/dd/yyyy ddMONyyyy ddmmyyyyy yymmdd mm.dd.yy dd/mm/yyyy hh:24:ss mm-dd-yyyy and so many . Also it could contains a free text data that may be numbers or text. I need the possible fastest way to retrieve it as a date , and if it's cannot match any date format to retrieve null

Answer 1

Said that this is really a bad and dangerous way to store dates, you could try with something like the following:

select myColumn,
       coalesce(
                to_date(myColumn default null on conversion error, 'dd/mm/yyyy' ),
                to_date(myColumn default null on conversion error, 'yyyy mm dd' ),
                ...                                                 /* all the known formats */
                to_date(myColumn default null on conversion error ) /* dangerous */
                )
from myTable

Here I would list all the possiblly expected formats, in the right order and trying to avoid the to_date without format mask, which can be really dangerous and give you unexpected results.