How to avoid invalid date error in a subquery

Navigation
1

In the code below, the "D" subquery returns only valid dates in the DAY column, however, when you try to filter that column by using the WHERE clause, Oracle displays the following error message:

  

"ORA-01839: The date is not valid for the specified month"

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

Code example without WHERE and without the 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;

Code sample 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;
    
asked by anonymous 21.06.2018 / 16:52

2 answers

1

Try 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 subqueries.

    
21.06.2018 / 20:32
0

It may be that your bank's date and time formatting is not in the same pattern you are trying to compare.

Check the formatting of date and time with the command: 

select sysdate from dual.

If you have 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 do the conversion, and then the comparison: 

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

In your case, it would look 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 permanently change 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 this response how -to-change-default-nls-date-format-for-oracle-jdbc-client

    
21.06.2018 / 19:10
Why does this error of name 'sys' is not defined? How to change all selected words in one go in VScode