오라클 주간 요일 날짜 구하기

오라클에서 입력된 날짜에 해당하는 주차의 월요일 ~ 일요일까지 날짜와 요일을 구할수 있다.

 

SELECT  1 AS gubun, TO_CHAR(NEXT_DAY((CASE WHEN TO_CHAR(TO_DATE('20140319'), 'd') >= 2 THEN TO_DATE('20140319')-6 ELSE TO_DATE('20140319') END) + 7*:cpoint, 2), 'YYYYMMDD') AS days, TO_CHAR(NEXT_DAY((CASE WHEN TO_CHAR(TO_DATE('20140319'), 'd') >= 2 THEN TO_DATE('20140319')-6 ELSE TO_DATE('20140319') END) + 7*:cpoint, 2), 'dy') AS weeks FROM DUAL
UNION
SELECT  2, TO_CHAR(NEXT_DAY((CASE WHEN TO_CHAR(TO_DATE('20140319'), 'd') >= 3 THEN TO_DATE('20140319')-6 ELSE TO_DATE('20140319') END) + 7*:cpoint, 3), 'YYYYMMDD'), TO_CHAR(NEXT_DAY((CASE WHEN TO_CHAR(TO_DATE('20140319'), 'd') >= 3 THEN TO_DATE('20140319')-6 ELSE TO_DATE('20140319') END) + 7*:cpoint, 3), 'dy') FROM DUAL
UNION
SELECT  3, TO_CHAR(NEXT_DAY((CASE WHEN TO_CHAR(TO_DATE('20140319'), 'd') >= 4 THEN TO_DATE('20140319')-6 ELSE TO_DATE('20140319') END) + 7*:cpoint, 4), 'YYYYMMDD'), TO_CHAR(NEXT_DAY((CASE WHEN TO_CHAR(TO_DATE('20140319'), 'd') >= 4 THEN TO_DATE('20140319')-6 ELSE TO_DATE('20140319') END) + 7*:cpoint, 4), 'dy') FROM DUAL
UNION
SELECT  4, TO_CHAR(NEXT_DAY((CASE WHEN TO_CHAR(TO_DATE('20140319'), 'd') >= 5 THEN TO_DATE('20140319')-6 ELSE TO_DATE('20140319') END) + 7*:cpoint, 5), 'YYYYMMDD'), TO_CHAR(NEXT_DAY((CASE WHEN TO_CHAR(TO_DATE('20140319'), 'd') >= 5 THEN TO_DATE('20140319')-6 ELSE TO_DATE('20140319') END) + 7*:cpoint, 5), 'dy') FROM DUAL
UNION
SELECT  5, TO_CHAR(NEXT_DAY((CASE WHEN TO_CHAR(TO_DATE('20140319'), 'd') >= 6 THEN TO_DATE('20140319')-6 ELSE TO_DATE('20140319') END) + 7*:cpoint, 6), 'YYYYMMDD'), TO_CHAR(NEXT_DAY((CASE WHEN TO_CHAR(TO_DATE('20140319'), 'd') >= 6 THEN TO_DATE('20140319')-6 ELSE TO_DATE('20140319') END) + 7*:cpoint, 6), 'dy') FROM DUAL
UNION
SELECT  6, TO_CHAR(NEXT_DAY((CASE WHEN TO_CHAR(TO_DATE('20140319'), 'd') >= 7 THEN TO_DATE('20140319')-6 ELSE TO_DATE('20140319') END) + 7*:cpoint, 7), 'YYYYMMDD'), TO_CHAR(NEXT_DAY((CASE WHEN TO_CHAR(TO_DATE('20140319'), 'd') >= 7 THEN TO_DATE('20140319')-6 ELSE TO_DATE('20140319') END) + 7*:cpoint, 7), 'dy') FROM DUAL
UNION
SELECT  7, TO_CHAR(NEXT_DAY((CASE WHEN TO_CHAR(TO_DATE('20140319'), 'd') >= 1 THEN TO_DATE('20140319') ELSE TO_DATE('20140319') END) + 7*:cpoint, 1), 'YYYYMMDD'), TO_CHAR(NEXT_DAY((CASE WHEN TO_CHAR(TO_DATE('20140319'), 'd') >= 1 THEN TO_DATE('20140319') ELSE TO_DATE('20140319') END) + 7*:cpoint, 1), 'dy') FROM DUAL;

 

cpoint에 0을 입력하면 입력된 날짜에 해당하는 주..

-1은 전주.. -2는 전전주.. 1은 차주.. 2는 차차주..

 

1    월    20140317

2    화    20140318

3    수    20140319

4    목    20140320

5    금    20140321

6    토    20140322

7    일    20140323

 

이런식으로 계산된다..