Oracle TO_DATE throws ORA-01843 when used in WHERE but not in SELECT

I have a problem with a (relativ) simple query which I do not understand, and I hope someone can help me on this.

Here we have the query:

SELECT TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
       TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
  FROM (SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ') a
       WHERE  
        trunc(SYSDATE)
        BETWEEN 
         TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
        AND 
         TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD')

The curious thing in this query is that it throw a ORA-01843 but only with the WHERE clause if I remove the WHERE clause no error is thrown.

So this works

SELECT TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
       TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
  FROM (SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ') a

Since the WHERE part is using the exact same things as the SELECT part I am asking myself how this is possible?

Some background information:

• The datatype of the both columns (ABWENDDAT, ABWBEGDAT) is VARCHAR2(14)
• I checked the content of the columns we only have two entries which will trigger this exception (entries are: 99999999999999) but this two entries are filtered with the WHERE ABWABTNR <> 'PASRZ' clause.
• I also did make sure all rows (per statement) are returned, so after I execute a statement I go through all the rows (until the end) returned.

I also checked stackoverflow and found some questions which go in the same direction but I did not find a question with a answer which works for me or explain the behaviour.

I think the reason for this behaviour can be the execution plan (or precidence of execution). So the two rows which can trigger the error are filterd after the WHERE which throws the ORA-01843 but before the SELECT part. can this be true and if so, do someone have a idea how I can change the query so this is working?

Thank you in advance!

It is possible that the predicate

TRUNC(sysdate) BETWEEN TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
                    AND TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD')

is being pushed into your inner query, which should be filtering out the non-dates.

(SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ') 

which means this query effectively becomes

SELECT * 
  FROM babw 
 WHERE ABWABTNR <> 'PASRZ'
   AND TRUNC(sysdate) BETWEEN TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
                          AND TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD')

causing your error

The "best" things to do are, in order,

• Only store dates in a column with a date datatype
• Find and correct the incorrect dates

In the absence of either of these occurring you could use the no_push_pred hint to avoid pushing the predicate into the inner query

SELECT /*+ no_push_pred(a) */
      TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
       TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
  FROM (SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ') a
       WHERE  
        trunc(SYSDATE)
        BETWEEN 
         TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
        AND 
         TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD')

The SQL Engine is opting to rewrite your query without the nested sub-queries so your first query is effectively:

SELECT TO_DATE (SUBSTR (ABWENDDAT, 1, 8), 'YYYYMMDD'),
       TO_DATE (SUBSTR (ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM   babw
WHERE  ABWABTNR <> 'PASRZ'
AND    trunc(SYSDATE) BETWEEN TO_DATE (SUBSTR (ABWBEGDAT, 1, 8), 'YYYYMMDD')
                      AND     TO_DATE (SUBSTR (ABWENDDAT, 1, 8), 'YYYYMMDD')

and the BETWEEN clause is being evaluated before the ABWABTNR comparison.

You can try using hints to solve the issue. Either:

• /*+ no_push_pred(a) */ in the outer query; or
• /*+ no_merge */ in the inner query.

Or you can materialize the inner query using ROWNUM:

SELECT TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
       TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM   (
  SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ' AND ROWNUM > 0
) a
WHERE  trunc(SYSDATE) BETWEEN TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
                      AND     TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD');

Or you can use a CASE expression:

SELECT TO_DATE (SUBSTR (ABWENDDAT, 1, 8), 'YYYYMMDD'),
       TO_DATE (SUBSTR (ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM   babw
WHERE  CASE
       WHEN ABWABTNR <> 'PASRZ'
       AND  trunc(SYSDATE) BETWEEN TO_DATE (SUBSTR (ABWBEGDAT, 1, 8), 'YYYYMMDD')
                           AND     TO_DATE (SUBSTR (ABWENDDAT, 1, 8), 'YYYYMMDD')
       THEN 1
       END = 1;

It is possible because not just every row in that table has both a.ABWENDDAT and a.ABWENDDAT such as to be convertible to date with that format.
In case you have Oracle version >12 then you may use TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8) default null on conversion error, 'YYYYMMDD'). If you have one row where this conversion will not be possible and you do not have default null on conversion error, then that single row will cause the exception.
Or, if Oracle version <12, then you should first filter out the rows in which the data in those columns cannot be converted to date using a common-table expression.