0
How to generate the interval of all days of the months between two dates?
Considering only the parameters:
Starting Date: 01/10/2014
End Date: 12/29/2018
Example:
01/01/2014
02/01/2014
03/01/2014
...
26/12/2018
27/12/2018
28/12/2018
29/12/2018
30/12/2018
31/12/2018
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
;
Browser other questions tagged oracle
You are not signed in. Login or sign up in order to post.
I found a solution at:(https://sqlshow.wordpress.com/2013/05/13/calendario-em-sql-oracle/#comment-61) and changed a little suggesting improvements!
– AndyDaSilva52
@Rovannlinhalis any suggestions, should I remove the article and comment on the other? I took a look and are similar situations, but there is no presence of the person in my case, only two date intervals
– AndyDaSilva52
I do not know if it is necessary to remove this, because it serves as another way to reach a result, I only found valid the mention as duplicate because the solution is practically the same.
– Rovann Linhalis
As @Rovannlinhalis cites the answer is another way, it is related to the other question, but it is of more general use, because the answer here can be applied in a view for example, while the other question uses advanced concepts and has a relationship table
– AndyDaSilva52