[Oracle Sql] 요일 달력 쿼리 만들기

Job/Oracle

reallllbro 2014. 4. 25. 10:58

/*****************************************************************************/

--달력 쿼리

SELECT MIN (DECODE (TO_CHAR (DAYS, 'D'), 1, TO_CHAR (DAYS, 'FMDD'))) AS SUN,

MIN (DECODE (TO_CHAR (DAYS, 'D'), 2, TO_CHAR (DAYS, 'FMDD'))) AS MON,

MIN (DECODE (TO_CHAR (DAYS, 'D'), 3, TO_CHAR (DAYS, 'FMDD'))) AS TUE,

MIN (DECODE (TO_CHAR (DAYS, 'D'), 4, TO_CHAR (DAYS, 'FMDD'))) AS WED,

MIN (DECODE (TO_CHAR (DAYS, 'D'), 5, TO_CHAR (DAYS, 'FMDD'))) AS THU,

MIN (DECODE (TO_CHAR (DAYS, 'D'), 6, TO_CHAR (DAYS, 'FMDD'))) AS FRI,

MIN (DECODE (TO_CHAR (DAYS, 'D'), 7, TO_CHAR (DAYS, 'FMDD'))) AS SAT

FROM ( SELECT BASE_MON + LEVEL - 1 AS DAYS,

( TRUNC(BASE_MON + LEVEL - 1, 'D') - TRUNC(TRUNC(BASE_MON + LEVEL - 1, 'Y'), 'D') ) / 7 + 1 AS WEEK_GRP

FROM ( SELECT TO_DATE ('2013' || '09', 'YYYYMM') AS BASE_MON FROM DUAL )

CONNECT BY BASE_MON + LEVEL - 1 <= LAST_DAY (BASE_MON) )

GROUP BY WEEK_GRP

ORDER BY WEEK_GRP;

/*****************************************************************************/

--요일 리턴

SELECT TRUNC(SYSDATE,'IW') FROM DUAL;

SELECT TO_CHAR(TRUNC(SYSDATE+,'IW'),'YYYYMMDD DAY') FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'D'),TO_CHAR(SYSDATE, 'DY') FROM DUAL

UNION ALL

SELECT TO_CHAR(SYSDATE+1, 'D'),TO_CHAR(SYSDATE+1, 'DY') FROM DUAL

UNION ALL

SELECT TO_CHAR(SYSDATE+2, 'D'),TO_CHAR(SYSDATE+2, 'DY') FROM DUAL

UNION ALL

SELECT TO_CHAR(SYSDATE+3, 'D'),TO_CHAR(SYSDATE+3, 'DY') FROM DUAL

UNION ALL

SELECT TO_CHAR(SYSDATE+4, 'D'),TO_CHAR(SYSDATE+4, 'DY') FROM DUAL

UNION ALL

SELECT TO_CHAR(SYSDATE+5, 'D'),TO_CHAR(SYSDATE+5, 'DY') FROM DUAL

UNION ALL

SELECT TO_CHAR(SYSDATE+6, 'D'),TO_CHAR(SYSDATE+6, 'DY') FROM DUAL

;

SELECT * FROM COM_ONLN_DRTM_PSTP_I WHERE DT > '20121231';

SELECT TO_CHAR(TO_DATE('20121225'), 'D'),TO_CHAR(TO_DATE('20121225'), 'DY') FROM DUAL;

/*****************************************************************************/