Oracle) 오늘 기준 특정 날짜 가져오기

Oracle 오늘 기준 특정 날짜 가져오기

오늘 기준 이전 3년 YEAR목록 가져오기

SELECT TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - LEVEL)  AS YYYY 
FROM DUAL 
CONNECT BY LEVEL <= 3

오늘 기준 +5일 DAY목록 가져오기 (오늘 제외)

 SELECT 
    (SYSDATE + LEVEL) AS DT_CAL, 
    TO_CHAR(SYSDATE + LEVEL, 'YYYYMMDD')  AS YYYYMMDD, 
    TO_CHAR(SYSDATE + LEVEL, 'YYYY-MM-DD')  AS DISPLAY_DATE 
FROM DUAL 
CONNECT BY LEVEL <= 5

오늘 기준 +5일 DAY목록 가져오기 (오늘 포함)

SELECT 
    (SYSDATE + LEVEL - 1) AS DT_CAL, 
    TO_CHAR(SYSDATE + LEVEL - 1, 'YYYYMMDD')  AS YYYYMMDD, 
    TO_CHAR(SYSDATE + LEVEL - 1, 'YYYY-MM-DD')  AS DISPLAY_DATE 
FROM DUAL 
CONNECT BY LEVEL <= 6

오늘 기준 WORKING DAY+5일 DAY목록 가져오기 (오늘 제외)

테이블 예시

• TABLE : CALENDAR_TB
• COLUMMN
  • YYYYMMDD VARCHAR2(8)
  • HOLIDAY_CD VARCHAR2(1) -- W: Woring Day(작업일)

SELECT
    TO_DATE(T.YYYYMMDD, 'YYYYMMDD') AS DT_CAL,
    T.YYYYMMDD                                             AS YYYYMMDD,
    TO_CHAR(TO_DATE(T.YYYYMMDD, 'YYYYMMDD'), 'YYYY-MM-DD') AS DISPLAY_DATE
    FROM
        (
            SELECT
                CAL.YYYYMMDD AS YYYYMMDD,
                ROW_NUMBER() OVER( ORDER BY CAL.YYYYMMDD ASC ) AS RN
            FROM
                CALENDAR_TB CAL
            WHERE
            	CAL.HOLIDAY_CD = 'W'
                AND CAL.YYYYMMDD BETWEEN TO_CHAR(SYSDATE + 1, 'YYYYMMDD') AND TO_CHAR(SYSDATE + 30, 'YYYYMMDD')
        ) T
    WHERE T.RN <= 5