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!

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;