오라클 SQL 문법 공부9 - MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY

서론

날짜형 데이터들은 단순히 숫자처럼 +1이나 +3으로도 일 수를 조정 할 수는 있지만 한달의 일자의 수는 각 달마다 다르므로 이 함수들을 사용해야 정확한 날짜를 찾거나 차이를 알수가 있다. 아주 간단한 날짜 계산식이라면 모를까 대부분은 아래 함수들을 가지고 계산을 한다.

본론

MONTHS_BETWEEN, ADD_MONTHS

--MONTHS_BETWEEN(날짜1, 날짜2)
--두 날짜 간의 개월 수 차이 값을 반환
--두 날짜 중 큰 날짜를 먼저 써야 양수가 나온다
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2017-01-30', 'YYYY-MM-DD')) FROM DUAL;

SELECT TRUNC(SYSDATE - HIREDATE) AS 입사일수,
TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS 입사월수,
TRUNC(TRUNC(SYSDATE - HIREDATE)/365) AS 근속년수
FROM EMP;

--ADD_MONTHS(날짜, n)
--날짜에서 n개월 이후 값을 반환
--음수인 경우 n개월 전
SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL;
SELECT ADD_MONTHS(SYSDATE, -1) FROM DUAL;

MONTHS_BETWEEN은 두 날짜간의 개월 수 차이를 알려준다. 단순히 두 날짜를 뺀값을 30으로 나눠 계산 할수도 있지만 정확한 달 수가 나올 수가 없다. (날짜형 - 날짜형을 하면 차이나는 만큼의 일 수를 구할 수 있다.)

근속년수라면 일자를 365로 나누면 년을 알 수가 있다. TRUNC을 사용하는 이유는 365로 나눈 소수점까지 표시가 될 수 있기 때문에 잘라버린다.

ADD_MONTHS는 몇 개월 후, 전의 날짜를 알려준다. 예를들어 3개월 이후가 궁금하다면 날짜형 데이터에  3개월 후의 일을 더해서 구할 수도 있지만 정확히 3개월의 일자를 구하기는 어렵다. 왜냐하면 서론에서도 말했다시피 달마다 일자의 수가 다르기 때문이다. 따라서 간단히 ADD_MONTHS 함수를 사용 하여 구해주면 된다. 

NEXT_DAY, LAST_DAY

--NEXt_DAY(날짜, 지정요일)*
--날짜 다음의 지정한 요일에 해당하는 날짜를 반환
--일요일은 1, 월요일은 2.. 토요일은 7로 지정요일 명시
--nls_date_language가 Koream으로 되어있는 경우에는 지정요일을 '월', '화' 처럼 명시 할 수 있다
--american 이면 'MON' 'SUN' 이런식*/
SELECT NEXT_DAY(SYSDATE, 1) FROM DUAL;
SELECT NEXT_DAY(SYSDATE, '월') FROM DUAL;

ALTER SESSION SET NLS_DATE_LANGUAGE = 'american';
SELECT NEXT_DAY(SYSDATE, 'MON') FROM DUAL;

--LAST_DAY(날짜)
--날짜의 해당 월의 마지막 날짜를 반환한다.
SELECT LAST_DAY(SYSDATE) FROM DUAL;

NEXT_DAY 는 해당 시간으로 부터 지정해준 요일이 언제 인지 알려 준다. 만약 해당 날짜가 화요일인데 월요일을 찾게 되면 다음 주 월요일의 날짜가 출력된다. 뒤의 날짜는 는 일요일 기준으로 1~ 월요일 7 까지 숫자로 표현 해줄 수 있으며 NLS_DATE_LANGUAGE 타입에 따라 한글로 쓰거나 영어로 써야 되는 경우가 발생한다.

LAST_DAY 는 해당 날짜 달의 마지막 날짜를 알려준다. 9월 이라면 글을 작성한 2017년도 9월의 날짜는 30일이라고 알려주는 것이다. 힘들게 달력을 펴가며 체크할 필요가 없다. 

실습문제

--1. emp테이블에서 현재까지 근무일수가 몇 주, 혹은 몇 일인가를 출력하여라.
--단, 근무일수가 많은 사람 순으로 출력하여라
select * from emp;
SELECT ENAME,
TRUNC((SYSDATE - HIREDATE) / 7) AS "근속주수",
TRUNC(SYSDATE - HIREDATE) AS "근속일수"/*,
mod(TRUNC(SYSDATE - HIREDATE), 7) AS "나머지근속일수" */
FROM EMP
ORDER BY "근속일수" DESC;

--2. emp테이블에서 10번 부서원의 현재까지의 근무 월수를 계산하여 출력하여라
SELECT ENAME,
TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE))
FROM EMP
WHERE DEPTNO = 10;

--3. emp테이블에서 입사한 달의 남은 근무 일수를 계산하여 출력하여라*/
단, 토요일과 일요일도 근무일수에 포함된다
SELECT ENAME,
HIREDATE,
LAST_DAY(HIREDATE) - HIREDATE FROM EMP;

--4. emp 테이블에서 10번 부서원의 입사 일자로부터 돌아오는 금요일을 계산하여 출력하여라
SELECT ENAME,
HIREDATE,
NEXT_DAY(HIREDATE, 6)
FROM EMP
WHERE DEPTNO = 10;

--5) emp 테이블을 이용하여 근속년수를 36년 8개월 27일(smith 기준)과 같이 표현할 수 있도록하여라.
select TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE) / 12) ||'년 ' 
|| trunc(mod(MONTHS_BETWEEN(SYSDATE, HIREDATE) , 12)) ||'개월 ' 
|| trunc(sysdate - add_months(hiredate, months_between(sysdate, hiredate)))||'일' 
from emp;
-- 일자를 구하는 것은 생각보다 헷갈릴 수도 있는데 입사일로부터 현재까지의 달 수를 구한 뒤에
-- 입사일에 그 달 수를 더한 값을 현재 날짜에서 빼면 일만 남는다.

결론

날짜를 다룰 수 있는 함수를 가지고 특히 실습문제 5번정도의 난이도를 풀 수 있을 정도의 이해를 가지면 될 것으로 보인다. 실습문제의 샘플데이터 들은 문법 공부4에 있으니 개발툴들을 이용하여 데이터를 집어 넣어 테스트 해보자.