본문 바로가기

DataBase/Oracle

[펌] 달력 SQL

-- 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 (dt + LEVEL , 'iw')
             ORDER BY sat );
 

[출처] [펌] 달력 SQL|작성자 시바

'DataBase > Oracle' 카테고리의 다른 글