ORACLE|달력(Calendar)

월별 달력 모양 표시 요일구하기 to_char(sysdate, 'd') 1.일요일, 2.월요일 .... 7.토요일 주(Week) 구하기 trunc(sysdate, 'iw')       -- 지정된 일자의 월요일 날짜 구하기 (일요일 경우 직전 월요일의 날짜)

-- Calendar SQL for Oracle
-- by NalCoder / valenny.tistory.com / valenny@naver.com
-- on 2009.7.30

SELECT *
  FROM (    SELECT MIN ( CASE WHEN TO_CHAR (dt + LEVEL - 1, 'd') = '1' THEN LEVEL
                              ELSE NULL END ) AS sun
                  ,MIN ( CASE WHEN TO_CHAR (dt + LEVEL - 1, 'd') = '2' THEN LEVEL
                              ELSE NULL END ) AS mon
                  ,MIN ( CASE WHEN TO_CHAR (dt + LEVEL - 1, 'd') = '3' THEN LEVEL
                              ELSE NULL END ) AS tue
                  ,MIN ( CASE WHEN TO_CHAR (dt + LEVEL - 1, 'd') = '4' THEN LEVEL
                              ELSE NULL END ) AS wed
                  ,MIN ( CASE WHEN TO_CHAR (dt + LEVEL - 1, 'd') = '5' THEN LEVEL
                              ELSE NULL END ) AS thu
                  ,MIN ( CASE WHEN TO_CHAR (dt + LEVEL - 1, 'd') = '6' THEN LEVEL
                              ELSE NULL END ) AS fri
                  ,MIN ( CASE WHEN TO_CHAR (dt + LEVEL - 1, 'd') = '7' THEN LEVEL
                              ELSE NULL END ) AS sat
              FROM (   SELECT TO_DATE(:yyyymm,'YYYYMM') AS dt
                         FROM dual) t_dt
           CONNECT BY LEVEL <= LAST_DAY (dt) - dt + 1
             GROUP BY 

TRUNC

TRUNC (dt + LEVEL , 'iw')
             ORDER BY sat );

지정된 년도의 모든 토/일요일(공휴일) 구하기

SELECT  HOLI_DATE

      , HOLI_TYPE

      , DECODE(HOLI_TYPE, 1, '일요일', 7, '토요일') AS HOLI_NAME

FROM (  SELECT  DT

              , LEVEL

              , DT + LEVEL - 1 AS HOLI_DATE

              , TO_CHAR(DT + LEVEL - 1, 'D') AS HOLI_TYPE

          FROM (   SELECT TO_DATE('20100101','YYYYMMDD') AS DT

                     FROM dual

                )

t_dt

VW_DATE

       CONNECT BY LEVEL <= 365

     )

WHERE HOLI_TYPE IN (1, 7);