[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

 

'개발 > DB' 카테고리의 다른 글

Mybatis #과 $의 차이  (1) 2014.11.10
몽고DB 설치  (0) 2014.01.22
테이블 정보(컬럼 정보, 코멘트 등등)  (0) 2013.11.11
테이블 사이즈 구하기(컬럼 길이, 컬럼 길이 평균)  (0) 2013.03.12
[ORACLE] 달력  (0) 2012.09.14
[ORACLE] 디비 락 해제  (0) 2012.01.09
  Comments,   0  Trackbacks
댓글 쓰기