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

I have the following query that I am attempting to use as a COMMAND in a crystal report that I am working on.

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

This works fine, however my only concern is that the date may not always be in the correct format (due to user error). I know that when the to_date function fails it throws an exception.. is it possible to handle this exception in such a way that it ignores the corresponding row in my SELECT statement? Because otherwise my report would break if only one date in the entire database is incorrectly formatted.

I looked to see if Oracle offers an isDate function, but it seems like you are supposed to just handle the exception. Any help would be greatly appreciated. Thanks!!

Solution 1:

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 

Solution 2:

If your data is not consistent and dates stored as strings may not be valid then you have 3 options.

1. Refactor your DB to make sure that the column stores a date datatype
2. Handle the exception of string to date in a stored procedure
3. Handle the exception of string to date in a (complex) record selection formula

I would suggest using the first option as your data should be consistent.

The second option will provide some flexibility and speed as the report will only fetch the rows that are needed.

The third option will force the report to fetch every record in the table and then have the report filter down the records.