Ordering VARCHAR2 dates in Oracle

oracle, sql

Solution

You could create a function on this column that tries to convert the date, but catches any exceptions, and sort by that.

You can also nest the exceptions and try several formats for conversion before defaulting to some date of your choosing. eg.

CREATE OR REPLACE FUNCTION my2date( p_str in VARCHAR2) RETURN DATE is

BEGIN
      RETURN to_date( p_str, 'MM/DD/YYYY' );
EXCEPTION WHEN OTHERS THEN
    BEGIN
        RETURN to_date( p_str, 'DD/MM/YYYY');
    EXCEPTION WHEN OTHERS THEN
        RETURN sysdate; /* or some other fixed date - depends on if you want these first or last */
    END;
END;
/

Then just use the clause:

ORDER BY my2date(MY_DATE_COLUMN) desc;

Problem

I have a column in a `VIEW` that contains date values in the format of 'MM/DD/YYYY'. If this column only contained date values in the format I listed, I could do an `ORDER BY` on the column with a `TO_DATE` on it to get the VIEW to sort by that date: ``` ORDER BY TO_DATE(MY_DATE_COLUMN, 'MM/DD/YYYY') desc ``` However, there's data in this column that's not formatted as 'MM/DD/YYYY'. Is there anyway just using `SQL` to be able to sort on this column being realized as a `DATE` column? I'm thinking that it's not possible due to the data that is not formatted as 'MM/DD/YYYY', but I'm not totally sure... I don't want to add another column in the `VIEW` to accomplish this as well.

Original source