[ORACLE] 달력

-- Ver. 1 --

 

SELECT    MIN(DECODE(TO_CHAR(ym+LEVEL-1,'D'),'1',LEVEL)) SUN,
                MIN(DECODE(TO_CHAR(ym+LEVEL-1,'D'),'2',LEVEL)) MON,
                MIN(DECODE(TO_CHAR(ym+LEVEL-1,'D'),'3',LEVEL)) TUE,
                MIN(DECODE(TO_CHAR(ym+LEVEL-1,'D'),'4',LEVEL)) WED,
                MIN(DECODE(TO_CHAR(ym+LEVEL-1,'D'),'5',LEVEL)) THU,
                MIN(DECODE(TO_CHAR(ym+LEVEL-1,'D'),'6',LEVEL)) FRI,
                MIN(DECODE(TO_CHAR(ym+LEVEL-1,'D'),'7',LEVEL)) SAT
FROM       (SELECT TO_DATE('200905','YYYYMM') ym FROM DUAL)
CONNECT BY LEVEL<=TO_CHAR(LAST_DAY(ym),'DD')
GROUP     BY TRUNC(ym+LEVEL,'IW')
ORDER     BY 7
;

 

-- Ver.2 --

 

SELECT MIN(DECODE(week,'1',day)) SUN,
             MIN(DECODE(week,'2',day)) MON,
             MIN(DECODE(week,'3',day)) TUE,
             MIN(DECODE(week,'4',day)) WED,
             MIN(DECODE(week,'5',day)) THU,
             MIN(DECODE(week,'6',day)) FRI,
             MIN(DECODE(week,'7',day)) SAT
FROM    (
              SELECT    level day, TO_CHAR(TO_DATE('200905','YYYYMM')+LEVEL-1,'D') week 
              FROM       DUAL
              CONNECT BY LEVEL<=TO_CHAR(LAST_DAY(TO_DATE('200905','YYYYMM')),'DD')
             )
GROUP  BY day+7-week
ORDER  BY 7 

출처는 요기

http://blog.naver.com/guile77/20092593668

 

[출처] [ORACLE] 달력 동적 생성 쿼리(2)|작성자 오디시