Geek asks and answers

Order VARCHAR2 at Oracle

I have a column in VIEWwhich contains date values ​​in the format "MM / DD / YYYY".

If this column contains only date values ​​in the specified format, I could do ORDER BYin column c TO_DATEon it to get a VIEW to sort by this date:

ORDER BY TO_DATE(MY_DATE_COLUMN, 'MM/DD/YYYY') desc

However, there is data in this column that is not formatted as "MM / DD / YYYY".

Anyway, just use SQLto sort by this column, which is implemented as a column DATE?

I think this is not possible due to data that is not formatted as "MM / DD / YYYY", but I'm not quite sure ...

I do not want to add another column in VIEWto accomplish this.

+3
source share
4 answers

You can create a function in this column that tries to convert the date, but catches any exceptions and sorts it.

You can also attach exceptions and try several formats for conversion, before choosing by default any day of your choice. 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 sentence:

ORDER BY my2date(MY_DATE_COLUMN) desc;
+4
source

One way is to manually check the date:

order by (case when substr(MY_DATE_COLUMN, 1, 2) between '01' and '12' and
                    substr(my_date_column, 3, 1) = '/' and
                    substr(my_date_column, 4, 2) between '01' and '31' and
                    substr(my_date_column, 5, 1) = '/' and
                    substr(my_date_column, 6, 4) between '1900' and '2100' and
                    len(my_date_column) = 10
               then to_date(MY_DATE_COLUMN, 'mm/dd/yyyy')
          end) desc

This is probably good enough for most purposes, although it will allow on February 30th.

What you really need is the IsDate function in Oracle, but it is not available.

I realized after posting this, that there might be an easier way:

order by substr(my_date_column, 6, 4) desc, substr(MY_DATE_COLUMN, 1, 2) desc,
         substr(my_date_column, 4, 2) desc

, . , , -. , , , , .

+4

, ... varchar...

+2

, - to_date(), null ( , sysdate). , to_date , .

CREATE OR REPLACE FUNCTION to_date_null(date_str IN varchar2, format_mask IN varchar2)
RETURN DATE
IS
  l_date DATE;
BEGIN
  l_date := to_date( date_str, format_mask );
  RETURN l_date;
EXCEPTION
  WHEN others THEN
    RETURN null;
END to_date_null;

order by

order by to_date_null(MY_DATE_COLUMN, 'MM/DD/YYYY') desc NULLS LAST;

NULLS FIRST NULLS LAST, , -.

+2

More articles:


All Articles