DBMS/오라클]오라클에서 해당월 날짜 구하기 / 오늘날짜 해당주 날짜 구하기

원본 출처 사이트 : http://daenamoo09.egloos.com/1182925  입니다.

아래의 내용은 제가 보기 편하기 위해 가져다 놓은 것입니다.

# 해당 일부터 30일 날짜

SELECT TO_CHAR(TO_DATE(BB.DAY, 'YYYYMMDD') + NUM, 'YYYY-MM-DD') AS DAY
FROM (
SELECT ROWNUM AS NUM
FROM DICTIONARY A,(
  SELECT B.DAY AS BDAY, C.DAY AS CDAY
  FROM
   ( SELECT TO_CHAR(SYSDATE,'YYYYMM') || '01' AS DAY FROM DUAL ) B,
   ( SELECT TO_CHAR(LAST_DAY(SYSDATE),'YYYYMMDD') AS DAY FROM DUAL ) C
  ) B
WHERE ROWNUM <= TO_DATE(B.CDAY, 'YYYYMMDD') - TO_DATE(B.BDAY, 'YYYYMMDD')  + 1) AA,
( SELECT TO_CHAR(SYSDATE - TO_NUMBER(TO_CHAR(SYSDATE,'D')) + 1,'YYYYMMDD') AS DAY FROM DUAL ) BB

# 현재 날짜에서 일주일 날짜..

SELECT TO_CHAR(TO_DATE(BB.DAY, 'YYYYMMDD') + NUM-1, 'YYYY-MM-DD') AS DAY
FROM (
SELECT ROWNUM AS NUM
FROM DICTIONARY A,(
  SELECT B.DAY AS BDAY, C.DAY AS CDAY
  FROM
   ( SELECT TO_CHAR(SYSDATE - TO_NUMBER(TO_CHAR(SYSDATE,'D')) + 1,'YYYYMMDD') AS DAY FROM DUAL ) B,
   ( SELECT TO_CHAR(SYSDATE - TO_NUMBER(TO_CHAR(SYSDATE,'D')) + 7,'YYYYMMDD') AS DAY FROM DUAL ) C
  ) B
WHERE ROWNUM <= TO_DATE(B.CDAY, 'YYYYMMDD') - TO_DATE(B.BDAY, 'YYYYMMDD')  + 1) AA,
( SELECT TO_CHAR(SYSDATE - TO_NUMBER(TO_CHAR(SYSDATE,'D')) + 1,'YYYYMMDD') AS DAY FROM DUAL ) BB

# 시작일부터 끝일까지

SELECT TO_CHAR(TO_DATE('[SDATE]', 'YYYY-MM-DD') + NUM-1, 'YYYY-MM-DD') AS DAY
FROM (
 SELECT ROWNUM NUM
 FROM DICTIONARY
 WHERE ROWNUM <= TO_DATE('[EDATE]', 'YYYY-MM-DD') - TO_DATE('[SDATE]', 'YYYY-MM-DD')  + 1
)

# 날짜계산

/* 어제 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE-1)
+0.99999421
/* 오늘 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 0.99999421
/* 내일 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE+1) AND TRUNC(SYSDATE+1)
+0.99999421
/* 금주 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE+1)-TO_CHAR(SYSDATE,'D')
AND TRUNC(SYSDATE+1)-TO_CHAR(SYSDATE,'D')
+6.99999421
/* 차주 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE+8)-TO_CHAR(SYSDATE, 'D')
AND TRUNC(TRUNC(SYSDATE)+14.99999421)-TO_CHAR
(SYSDATE, 'D')
/* 금월 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE+1)-TO_CHAR(SYSDATE,'DD')
AND TRUNC(LAST_DAY(SYSDATE))+0.99999421
/* 전월 */ 날짜칼럼 BETWEEN TRUNC(ADD_MONTHS(SYSDATE,-1)+1)-TO_CHAR
(SYSDATE,'DD')
AND TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))
+0.99999421
/* 차월 */ 날짜칼럼 BETWEEN ADD_MONTHS(TRUNC(SYSDATE),1)-TO_CHAR
(SYSDATE,'DD')+1
AND LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE),1)
+0.99999421)

# 특정일 까지의 간격을 년, 개월, 일로 표현하기

SELECT
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('19970101', 'YYYYMMDD'))/12) "년",
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('19970101', 'YYYYMMDD')) -
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('19970101', 'YYYYMMDD'))/12) *
12) "개월",
TRUNC((MONTHS_BETWEEN(SYSDATE,TO_DATE('19970101', 'YYYYMMDD')) -
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('19970101', 'YYYYMMDD')))) *
30.5) "일"