How to handle to_date exceptions in a SELECT statment to ignore those rows?

Echoing Tony’s comment, you’d be far better off storing dates in DATE columns rather than forcing a front-end query tool to find and handle these exceptions.

If you’re stuck with an incorrect data model, however, the simplest option in earlier versions is to create a function that does the conversion and handles the error,

CREATE OR REPLACE FUNCTION my_to_date( p_date_str IN VARCHAR2,
                              p_format_mask IN VARCHAR2 )
  RETURN DATE
IS
  l_date DATE;
BEGIN
  l_date := to_date( p_date_str, p_format_mask );
  RETURN l_date;
EXCEPTION
  WHEN others THEN
    RETURN null;
END my_to_date;

Your query would then become

SELECT * 
  FROM myTable
 WHERE my_to_date(myTable.sdate, 'MM/dd/yyyy') <= {?EndDate}

Of course, you’d most likely want a function-based index on the MY_TO_DATE call in order to make this query reasonably efficient.

In 12.2, Oracle has added extensions to the to_date and cast functions to handle conversions that error

SELECT * 
  FROM myTable
 WHERE to_date(myTable.sdate default null on conversion error, 'MM/dd/yyyy') <= {?EndDate}

You could also use the validate_conversion function if you’re looking for all the rows that are (or are not) valid dates.

SELECT *
  FROM myTable 
 WHERE validate_conversion( myTable.sdate as date, 'MM/DD/YYYY' ) = 1

More Related Contents:

Leave a Comment Cancel reply