반응형
[Oracle] oracle 달력 만들기 및 날짜 쿼리 활용하기
안녕하세요. 갓대희 입니다. 이번 포스팅은 [ 오라클 쿼리로 날짜 만들기 ] 입니다. : )
0. 달력을 만들기 앞서
가끔 오라클을 통해 달력을 만들어 활용해야하는 경우가 있다.
오라클 쿼리로 달력을 만들면서, 이때 사용한 쿼리와 관련된 날짜와 관련된 함수들도 알아볼 예정이다.
▶ 1. 해당월의 첫날 부터 마지막 일까지 가져오기
SELECT TO_CHAR (FIRST_DAY + LEVEL - 1, 'D') DAYS -- 요일(숫자)
, TO_CHAR (FIRST_DAY + LEVEL - 1, 'MM-DD') TODAY -- 해당일자
, TO_CHAR (FIRST_DAY + LEVEL - 1, 'YYYYMMDD') TODAY_STR -- 해당일자str
, TO_CHAR(FIRST_DAY + LEVEL - 1,'IW') WEEK_NUM -- 주차수
, TO_CHAR(FIRST_DAY + LEVEL - 1,'DY') DAYS_K -- 요일(한글)
FROM (SELECT TRUNC(SYSDATE, 'MM') FIRST_DAY FROM DUAL)
CONNECT BY FIRST_DAY + LEVEL - 1 <= TRUNC(LAST_DAY(SYSDATE))
▶ 2. 달력 모양으로 가공하기
SELECT CAL.*
FROM
(
SELECT COUNT (*) OVER (ORDER BY WEEK_NUM) WEEK_NUM
, TO_DATE (MIN (TODAY_STR) || '000000', 'YYYYMMDDHH24MISS') week_strt
, TO_DATE (MAX (TODAY_STR) || '235959', 'YYYYMMDDHH24MISS') week_end
, MIN (DECODE (DAYS, 2, TODAY)) MON
, MIN (DECODE (DAYS, 3, TODAY)) TUE
, MIN (DECODE (DAYS, 4, TODAY)) WED
, MIN (DECODE (DAYS, 5, TODAY)) THU
, MIN (DECODE (DAYS, 6, TODAY)) FRI
, MIN (DECODE (DAYS, 7, TODAY)) SAT
, MIN (DECODE (DAYS, 1, TODAY)) SUN
FROM
(
SELECT TO_CHAR (FIRST_DAY + LEVEL - 1, 'D') DAYS -- 요일(숫자)
, TO_CHAR (FIRST_DAY + LEVEL - 1, 'MM-DD') TODAY -- 해당일자
, TO_CHAR (FIRST_DAY + LEVEL - 1, 'YYYYMMDD') TODAY_STR -- 해당일자str
, TO_CHAR(FIRST_DAY + LEVEL - 1,'IW') WEEK_NUM -- 주차수
, TO_CHAR(FIRST_DAY + LEVEL - 1,'DY') DAYS_K -- 요일(한글)
FROM (SELECT TRUNC(SYSDATE, 'MM') FIRST_DAY FROM DUAL)
CONNECT BY FIRST_DAY + LEVEL - 1 <= TRUNC(LAST_DAY(SYSDATE))
)
GROUP BY WEEK_NUM
) CAL
;
해당 쿼리를 실행하면 현재 기준인 2020년 4월 달력의 모습을 가져왔다.
급한경우엔 해당 쿼리를 응용하여 바로 사용하고,
이 쿼리를 만들면서 사용한 관련 쿼리 내용들도 살펴보자.
▶ 3. 오라클 날짜 함수 관련 내용
1) Date Format Elements
Format | 설명 |
YYYY | 4자리 연도 |
YY | 두자리 연도 |
D | Day of week (1-7) |
DAY | 요일(월,화..) |
DD | Day of month (1-31) |
DDD | Day of year (1-366) |
MM | 해당월을 01~12로 표시 |
MONTH | Name of month |
WW | Week of year (1-53) |
IW | Week of year (1-53) 국제 표준 |
W | Week of month (1-5) |
HH24 | Hour of day (0-23) |
HH | Hour of day (1-12) |
MI | Minute (0-59) |
SS | Second (0-59) |
- "WW"와 "IW" 모두 1년의 몇 주차(1~53)로 조회하는 포맷 이다.
- "WW" : 1일에서 7일까지가 1주차로 시작
- "IW" : 실제 달력에 맞게 주차가 계산
2) TO_DATE
- TO_DATE 함수는 CHAR, VARCHAR2형을 DATE 타입으로 변환한다.
- TO_DATE(char, format) 형식으로 사용 할 수 있다.
- Date Format Elements 에서 'W', 'WW' Format을 제외한 나머지는 TO_DATE 함수의 format으로 사용 할 수 있다.
3) TO_CHAR
- TO_CHAR 함수는 DATE형, NUMBER형을 문자 타입으로 변환하는 함수이다.
ex) 1. 주차 계산
-- 해당일의 주차
SELECT TO_CHAR(SYSDATE,'IW') FROM DUAL;
-- 해당 주의 첫번째 일자 [주초 일자]
SELECT
SYSDATE AS 오늘
, SYSDATE - 7 AS "7일전"
, TRUNC(SYSDATE, 'W') AS 해당월1일_요일_기준 --해당월1일 요일을 기준한 주초일자
, TRUNC(SYSDATE, 'IW') AS 월요일_기준
, TRUNC(SYSDATE, 'DY') AS 일요일_기준
FROM DUAL;
/*
오늘 7일전 해당월1일_요일_기준 월요일_기준 일요일_기준
2020/04/14 오후 4:59:59 2020/04/07 오후 4:59:59 2020/04/08 2020/04/13 2020/04/12
*/
ex) 2. 요일
SELECT SYSDATE FROM DUAL; -- 2020/04/14 오후 4:59:33
SELECT TO_CHAR(SYSDATE,'D') FROM DUAL; -- 3
SELECT TO_CHAR(SYSDATE, 'DY') FROM DUAL; -- 화
SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL; -- 화요일
SELECT TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') FROM DUAL; -- TUE
SELECT TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE=ENGLISH') FROM DUAL; -- TUESDAY
ex) 3. 요일로 날짜 계산
-- NEXT_DAY(날짜, '요일' OR 숫자)
SELECT NEXT_DAY((TRUNC(SYSDATE,'MM')-1),'월') FROM DUAL; --2020/04/06
SELECT NEXT_DAY((TRUNC(SYSDATE,'MM')-1),'화요일') FROM DUAL; -- 2020/04/07
SELECT NEXT_DAY((TRUNC(SYSDATE,'MM')-1),3) FROM DUAL; -- 2020/04/07
-- 당월 마지막 주 특정 요일
SELECT NEXT_DAY((LAST_DAY(SYSDATE)-7),'월') FROM DUAL; -- 2020/04/27 오후 5:07:03
SELECT NEXT_DAY((LAST_DAY(SYSDATE)-7),'화요일') FROM DUAL; -- 2020/04/28 오후 5:07:12
SELECT NEXT_DAY((LAST_DAY(SYSDATE)-7),3) FROM DUAL; -- 2020/04/28 오후 5:07:16
-- 월요일기준
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY') 오늘날짜
, TO_CHAR(TRUNC(SYSDATE,'IW') ,'YYYY-MM-DD DAY') 월
, TO_CHAR(TRUNC(SYSDATE,'IW')+1,'YYYY-MM-DD DAY') 화
, TO_CHAR(TRUNC(SYSDATE,'IW')+2,'YYYY-MM-DD DAY') 수
, TO_CHAR(TRUNC(SYSDATE,'IW')+3,'YYYY-MM-DD DAY') 목
, TO_CHAR(TRUNC(SYSDATE,'IW')+4,'YYYY-MM-DD DAY') 금
, TO_CHAR(TRUNC(SYSDATE,'IW')+5,'YYYY-MM-DD DAY') 토
, TO_CHAR(TRUNC(SYSDATE,'IW')+6,'YYYY-MM-DD DAY') 일
FROM DUAL;
-- 일요일기준
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY') 오늘날짜
, TO_CHAR(TRUNC(SYSDATE,'D') ,'YYYY-MM-DD DAY') 일
, TO_CHAR(TRUNC(SYSDATE,'D')+1,'YYYY-MM-DD DAY') 월
, TO_CHAR(TRUNC(SYSDATE,'D')+2,'YYYY-MM-DD DAY') 화
, TO_CHAR(TRUNC(SYSDATE,'D')+3,'YYYY-MM-DD DAY') 수
, TO_CHAR(TRUNC(SYSDATE,'D')+4,'YYYY-MM-DD DAY') 목
, TO_CHAR(TRUNC(SYSDATE,'D')+5,'YYYY-MM-DD DAY') 금
, TO_CHAR(TRUNC(SYSDATE,'D')+6,'YYYY-MM-DD DAY') 토
FROM DUAL;
ex) 4. etc
-- 당월 마지막 날짜
SELECT LAST_DAY(SYSDATE) FROM DUAL;
-- 당월 첫째 날
SELECT TRUNC(SYSDATE,'MM') FROM DUAL;
-- 다음달 첫째 날
SELECT ADD_MONTHS( TRUNC(SYSDATE,'MM'), 1) FROM DUAL;
-- 해당일 포함 된 주의 목요일 날짜
SELECT CASE WHEN TO_CHAR(TO_DATE('20100629','YYYYMMDD'), 'D') < 5 THEN TRUNC(TO_DATE('20100629','YYYYMMDD')-5, 'IW')+3
ELSE TRUNC(TO_DATE('20100629','YYYYMMDD'), 'IW')+3 END RESULT
FROM DUAL;
-- 당월 마지막 날의 요일 구하기
SELECT TO_CHAR(LAST_DAY(SYSDATE),'D') FROM DUAL;
-- 마지막주 월요일
SELECT NEXT_DAY((LAST_DAY(SYSDATE)-7),2)-1 FROM DUAL;
-- 마지막주 목요일
SELECT NEXT_DAY((LAST_DAY(SYSDATE)-7),5) FROM DUAL;
반응형
'4. Database > 4_1 쿼리_Func_Proc' 카테고리의 다른 글
[Oracle] 핸드폰번호 마스킹(휴대폰번호 마스킹) (2) | 2020.06.16 |
---|---|
[Oracle] Oracle 달력 만들기 및 날짜 쿼리 활용하기 (1) | 2020.04.15 |
[Oracle] 오라클에서 Java를 이용하여 함수 만들기 (샘플) (0) | 2017.11.30 |
[Oracle] 사업자번호 유효성 체크 Function (오라클) (0) | 2017.11.29 |
비밀댓글입니다
답글