How to avoid invalid date error in a sub-shipment

Question

In the code below, subconsultation "D" returns only valid dates in the DIA column, however, when trying to filter this column using the WHERE clause, Oracle displays the following error message:

"ORA-01839: date not valid for specified month"

How do I filter this column in the WHERE clause without this error appearing?

Example of code without WHERE and without error:

SELECT 
   D.DIA,
   D.MES_ANO,
   D.ULTIMO_DIA_MES
FROM
   (
   SELECT
      TO_DATE(LPAD(TO_CHAR(C.DIA),2,'0') || '/' || C.MES_ANO, 'DD/MM/YYYY') DIA,
      C.MES_ANO,
      C.ULTIMO_DIA_MES
   FROM
      (
      SELECT
        B.DIA,
         A.MES_ANO,
         A.ULTIMO_DIA_MES
      FROM
         (
            SELECT '04/2018' MES_ANO, TO_DATE('30/04/2018','DD/MM/YYYY') ULTIMO_DIA_MES FROM DUAL UNION ALL
            SELECT '05/2018' MES_ANO, TO_DATE('31/05/2018','DD/MM/YYYY') ULTIMO_DIA_MES FROM DUAL UNION ALL
            SELECT '06/2018' MES_ANO, TO_DATE('30/06/2018','DD/MM/YYYY') ULTIMO_DIA_MES FROM DUAL 
         ) A,
         ( SELECT ROWNUM DIA FROM DUAL CONNECT BY ROWNUM <= 31 ) B 
      WHERE
         B.DIA <= TO_NUMBER(TO_CHAR(A.ULTIMO_DIA_MES, 'DD'))
      ) C
   ) D 
ORDER BY 
   D.DIA;  

Example of code with WHERE and error:

SELECT 
   D.DIA,
   D.MES_ANO,
   D.ULTIMO_DIA_MES
FROM
   (
   SELECT
      TO_DATE(LPAD(TO_CHAR(C.DIA),2,'0') || '/' || C.MES_ANO, 'DD/MM/YYYY') DIA,
      C.MES_ANO,
      C.ULTIMO_DIA_MES
   FROM
      (
      SELECT
        B.DIA,
         A.MES_ANO,
         A.ULTIMO_DIA_MES
      FROM
         (
            SELECT '04/2018' MES_ANO, TO_DATE('30/04/2018','DD/MM/YYYY') ULTIMO_DIA_MES FROM DUAL UNION ALL
            SELECT '05/2018' MES_ANO, TO_DATE('31/05/2018','DD/MM/YYYY') ULTIMO_DIA_MES FROM DUAL UNION ALL
            SELECT '06/2018' MES_ANO, TO_DATE('30/06/2018','DD/MM/YYYY') ULTIMO_DIA_MES FROM DUAL 
         ) A,
         ( SELECT ROWNUM DIA FROM DUAL CONNECT BY ROWNUM <= 31 ) B 
      WHERE
         B.DIA <= TO_NUMBER(TO_CHAR(A.ULTIMO_DIA_MES, 'DD'))
      ) C
   ) D   
WHERE 
   D.DIA < SYSDATE
ORDER BY 
   D.DIA;  

Answer 1

Try it like this:

SELECT D.DIA, D.MES_ANO, D.ULTIMO_DIA_MES
FROM
   (SELECT
      CASE WHEN C.DIA < 10 THEN '0' ELSE '' END || TO_CHAR(C.DIA) || '/' || C.MES_ANO DIA,
      C.ANO_MES || CASE WHEN C.DIA < 10 THEN '0' ELSE '' END || TO_CHAR(C.DIA) DIA_REVERSO,
      C.MES_ANO,
      C.ULTIMO_DIA_MES
   FROM
      (
      SELECT
        B.DIA,
         A.MES_ANO,
         A.ANO_MES,
         A.ULTIMO_DIA_MES
      FROM
         (
            SELECT '04/2018' MES_ANO, '201804' ANO_MES, EXTRACT(DAY FROM TO_DATE('30/04/2018','DD/MM/YYYY')) ULTIMO_DIA_MES FROM DUAL UNION ALL
            SELECT '05/2018' MES_ANO, '201805' ANO_MES, EXTRACT(DAY FROM TO_DATE('31/05/2018','DD/MM/YYYY')) ULTIMO_DIA_MES FROM DUAL UNION ALL
            SELECT '06/2018' MES_ANO, '201806' ANO_MES, EXTRACT(DAY FROM TO_DATE('30/06/2018','DD/MM/YYYY')) ULTIMO_DIA_MES FROM DUAL 
         ) A,
         ( SELECT ROWNUM DIA FROM DUAL CONNECT BY ROWNUM <= 31 ) B 
      WHERE
         B.DIA <= A.ULTIMO_DIA_MES
      ) C
   ) D
WHERE D.DIA_REVERSO < TO_CHAR(SYSDATE, 'YYYYMMDD')
ORDER BY TO_DATE(D.DIA, 'DD/MM/YYYY');

It seems that there is a bug in Oracle that makes it apply the condition in the outer WHERE clause, in the sub-consultations.

Answer 2

It may be that formatting date and time of your database is not in the same pattern as trying to compare.

Check the date and time formatting with the command:

select sysdate from dual.

If different, (in the example my bank is in a different format 06/21/2018 13:56:12) of the format that is trying to make the comparison make the conversion, and then the comparison:

select to_date('06/21/2018', 'MM/DD/RRRR') from dual;

In your case, it would be something like:

WHERE 
   D.DIA < to_date(sysdate, 'MM/DD/RRRR')

---

If you want to change the date format in your session to test:

ALTER SESSION SET nls_date_format='MM/DD/RRRR hh24:mi:ss';

---

If you want to exchange permanently in the bank, go to DBA / SYS:

alter system set nls_date_format='MM/DD/RRRR hh24:mi:ss' scope=spfile;

or take a look at that answer how-to-change-default-nls-date-format-for-oracle-jdbc-client