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

Question

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!!

Answer 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 

Answer 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.

Answer 3

I have the same problem... an old legacy database with varchar fields for dates and decades of bad data in the field. As much as I'd like to, I can't change the datatypes either. But I came up with this solution to find if a date is current, which seems to be what you're doing as well:

select * from MyTable
where regexp_like(sdate, '[0-1][0-9].[0-3][0-9].[0-9][0-9][0-9][0-9]')
         -- make sure it's in the right format and ignore rows that are not
and substr(sdate,7,10) || substr(sdate,1,2) || substr(sdate,4,5) >= to_char({?EndDate}, 'YYYYMMDD')
         -- put the date in ISO format and do a string compare

The benefit of this approach is it doesn't choke on dates like "February 30".

Answer 4

Starting from Oracle 12c there is no need to define a function to catch the conversion exception.

Oracle introduced an ON CONVERSION ERROR clause in the TO_DATE function.

Basically the clause suppress the error in converting of an invalid date string (typical errors are ORA-01843, ORA-01841, ORA-011861, ORA-01840) and returns a specified default value or null.

Example of usage

select to_date('2020-99-01','yyyy-mm-dd') from dual;
-- ORA-01843: not a valid month
select to_date('2020-99-01' default null on conversion error,'yyyy-mm-dd') from dual;
-- returns NULL
select to_date('2020-99-01' default '2020-01-01' on conversion error,'yyyy-mm-dd') from dual;
-- 01.01.2020 00:00:00

Solution for the Legacy Application

Let's assume there is a table with a date column stored as VARCHAR2(10)

select  * from tab;

DATE_CHAR 
----------
2021-01-01
2021-99-01

Using the above feature a VIRTUAL DATE column is defined, that either shows the DATE or NULL in case of the conversion error

alter table tab add (
  date_d DATE as (to_date(date_char default null on conversion error,'yyyy-mm-dd')) VIRTUAL
);

select * from tab;

DATE_CHAR  DATE_D             
---------- -------------------
2021-01-01 01.01.2021 00:00:00
2021-99-01  

The VIRTUAL column can be safely used because its format is DATE and if required an INDEX can be set up on it.

 select  * from tab where date_d = date'2021-01-01';

Answer 5

Since you say that you have "no access" to the database, I am assuming that you can not create any functions to help you with this and that you can only run queries?

If that is the case, then the following code should get you most of what you need with the following caveats: 1) The stored date format that you want to evaluate is 'mm/dd/yyyy'. If this is not the case, then you can alter the code to fit your format. 2) The database does not contain invalid dates such as Feb 30th.

First, I created my test table and test data:

create table test ( x number, sdate varchar2(20));
insert into test values (1, null);
insert into test values (2, '01/01/1999');
insert into test values (3, '1999/01/01');
insert into test values (4, '01-01-1999');
insert into test values (5, '01/01-1999');
insert into test values (6, '01-01/1999');
insert into test values (7, '12/31/1999');
insert into test values (8, '31/12/1999');
commit;

Now, the query:

WITH dates AS (
    SELECT x
         , sdate
         , substr(sdate,1,2) as mm
         , substr(sdate,4,2) as dd
         , substr(sdate,7,4) as yyyy
    FROM test
    WHERE ( substr(sdate,1,2) IS NOT NAN -- make sure the first 2 characters are digits
            AND to_number(substr(sdate,1,2))  between 1 and 12 -- and are between 0 and 12
            AND substr(sdate,3,1) = '/' -- make sure the next character is a '/'
            AND substr(sdate,4,2) IS NOT NAN -- make sure the next 2 are digits
            AND to_number(substr(sdate,4,2)) between 1 and 31 -- and are between 0 and 31
            AND substr(sdate,6,1) = '/' -- make sure the next character is a '/'
            AND substr(sdate,7,4) IS NOT NAN -- make sure the next 4 are digits
            AND to_number(substr(sdate,7,4)) between 1 and 9999 -- and are between 1 and 9999
          )
)
SELECT x, sdate
FROM dates
WHERE to_date(mm||'/'||dd||'/'||yyyy,'mm/dd/yyyy') <= to_date('08/01/1999','mm/dd/yyyy');

And my results:

X  SDATE
-  ----------
2  01/01/1999

The WITH statement will do most of the validating to make sure that the sdate values are at least in the proper format. I had to break out each time unit month / day / year to do the to_date evaluation because I was still getting an invalid month error when I did a to_date on sdate.

I hope this helps.

Answer 6

Trust this reply clarifies... there is no direct EXCEPTION HANDLER for invalid date. One easy way is given below once you know the format like DD/MM/YYYY then below given REGEXP_LIKE function will work like a charm. to_date() also will work, when invalid_date is found then cursor will goto OTHERS EXCEPTION. given below.

DECLARE
   tmpnum      NUMBER; -- (1=true; 0 = false)
   ov_errmsg   LONG;
   tmpdate     DATE;
   lv_date     VARCHAR2 (15);
BEGIN
   lv_date := '6/2/2018'; -- this will fail in *regexp_like* itself
   lv_date := '06/22/2018'; -- this will fail in *to_date* and will be caught in *exception WHEN OTHERS* block
   lv_date := '07/03/2018'; -- this will succeed 

   BEGIN
      tmpnum := REGEXP_LIKE (lv_date, '[0-9]{2}/[0-9]{2}/[0-9]{4}');

      IF tmpnum = 0
      THEN                                              -- (1=true; 0 = false)
         ov_errmsg := '1. INVALID DATE FORMAT ';
         DBMS_OUTPUT.PUT_LINE (ov_errmsg);
         RETURN;
      END IF;

      tmpdate := TO_DATE (lv_date, 'DD/MM/RRRR');
      --tmpdate := TRUNC (NVL (to_date(lv_date,'DD/MM/RRRR'), SYSDATE));

      tmpnum := 1;
   EXCEPTION
      WHEN OTHERS
      THEN
         BEGIN
            tmpnum := 0;
            ov_errmsg := '2. INVALID DATE FORMAT ';
            DBMS_OUTPUT.PUT_LINE (ov_errmsg || SQLERRM);
            RETURN;
         END;
   -- continue with your other query blocks
   END;

   -- continue with your other query blocks
   DBMS_OUTPUT.PUT_LINE (tmpnum);
END;