Oracle - How to generate every day (Calendar) between two Dates with different Years [duplicate]

0

How to generate the interval of all days of the months between two dates?

Considering only the parameters: Start Date: 10/01/2014
Final Date: 12/29/2018

Example:

01/01/2014
02/01/2014
03/01/2014
...
12/26/2018
12/27/2018
12/28/2018
12/29/2018
12/30/2018
12/31/2018

    
asked by anonymous 06.06.2018 / 03:03

1 answer

0

The following code can be used:

SELECT
 CAL.DATA

,TO_NUMBER(TO_CHAR(CAL.DATA,'DD')) AS Dia

,TO_NUMBER(TO_CHAR(CAL.DATA,'MM')) AS Mes

,TO_NUMBER(TO_CHAR(CAL.DATA,'YY')) AS "AnoYY"

,TO_NUMBER(TO_CHAR(CAL.DATA,'YYYY')) AS "AnoYYYY"

,TO_CHAR(CAL.DATA,'day') AS "Descr_Dia"

,TO_CHAR(CAL.DATA,'dy') AS "Descr_Dia_abrev"

,TO_CHAR(CAL.DATA,'Month') AS "Descr_Mes"

,TO_CHAR(CAL.DATA,'Mon') AS "Descr_Mes_abrev"

,TO_CHAR(CAL.DATA,'dd month yyyy') AS Data_texto

FROM (
SELECT
  (
    TO_DATE(SEQ.MM || SEQ.YYYY, 'MM/YYYY')-1
    -- Subtrai 1 por SEQ.NUM não começar em zero
  ) + SEQ.NUM AS "DATA" 
    FROM
    (
        SELECT RESULT NUM, 
        TO_CHAR(( -- Data Mínima
            TO_DATE('01/01/2014', 'DD/MM/YYYY')
            ) , 'MM') AS "MM",
        TO_CHAR(( -- Data Mínima
            TO_DATE('01/01/2014', 'DD/MM/YYYY')
            ) , 'YYYY') AS "YYYY"
        FROM
          (
          SELECT ROWNUM RESULT FROM DUAL CONNECT BY LEVEL <= (
                (
                -- Data Máxima
                LAST_DAY(TO_DATE('31/12/2018', 'DD/MM/YYYY'))
                -
                -- Data Mínima
                TRUNC(TO_DATE('01/01/2014', 'DD/MM/YYYY')) -- Sempre primeiro dia do mês
                ) + 1 -- Último dia do último ano
            )
          ) -- Quantas sequências para gerar pelo MAX

    ) SEQ
) CAL
;
    
06.06.2018 / 03:17