/*연도별*/
WITH WD AS
(
SELECT TO_DATE('2015-01-01', 'YYYY-MM-DD') AS FROM_DT
, TO_DATE('2020-07-30', 'YYYY-MM-DD') AS TO_DT
FROM DUAL
)
SELECT DT
FROM (
SELECT TO_CHAR(TO_CHAR(FROM_DT,'YYYY') + LEVEL -1) AS DT
FROM WD
CONNECT BY LEVEL <= TO_CHAR(TO_DT, 'YYYY') - TO_CHAR(FROM_DT,'YYYY')+1
)
/*월별*/
WITH WD AS
(
SELECT TO_DATE('2021-01-01', 'YYYY-MM-DD') AS FROM_DT
, TO_DATE('2021-12-30', 'YYYY-MM-DD') AS TO_DT
FROM DUAL
)
SELECT DT, DT2
FROM (
SELECT TO_CHAR(ADD_MONTHS(FROM_DT , (LEVEL-1)), 'YYYYMM') AS DT2 ,
TO_CHAR(ADD_MONTHS(FROM_DT , (LEVEL-1)), 'YYYY-MM') AS DT
FROM WD
CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DT , FROM_DT)+1
)
/*주별*/
WITH WD AS
(
SELECT TO_DATE('2021-03-01', 'YYYY-MM-DD') AS FROM_DT -- 시작일자
, TO_DATE('2021-07-17', 'YYYY-MM-DD') AS TO_DT -- 종료일자
FROM DUAL
)
SELECT DT
FROM
(
SELECT DT
FROM (
SELECT TO_CHAR(FROM_DT + (LEVEL-1), 'YYYY')|| '_' || TO_CHAR(FROM_DT + (LEVEL-1), 'WW') AS DT
FROM WD
CONNECT BY LEVEL <= (TO_DT - FROM_DT) + 1
)
)
GROUP BY DT
ORDER BY DT
/*일별*/
WITH WD AS
(
SELECT TO_DATE('2020-07-01', 'YYYY-MM-DD') AS FROM_DT -- 시작일자
, TO_DATE('2020-07-17', 'YYYY-MM-DD') AS TO_DT -- 종료일자
FROM DUAL
)
SELECT DT, DT2
FROM (
SELECT TO_CHAR(FROM_DT + (LEVEL-1), 'YYYYMMDD') AS DT ,
TO_CHAR(FROM_DT + (LEVEL-1), 'YYYY-MM-DD') AS DT2
FROM WD
CONNECT BY LEVEL <= (TO_DT - FROM_DT) + 1
)