함수/단일행 처리 함수) 날짜 처리 함수(add_months, extract, trunc, months_between, next_day, last_day)

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

---

FUNCTION

- 일련의 작업 절차를 모아 놓은 database 객체

- 호출 시 인자를 전달하고 리턴 값을 받아 처리

- SQL의 function은 반드시 리턴 값을 가짐

함수 유형
단일행 처리 함수	문자 처리 함수
	숫자 처리 함수
	날짜 처리 함수
	형변환 처리 함수
	기타 함수
그룹 처리 함수	그룹마다 호출

---

단일행 처리 함수

- 매 행마다 함수 호출, 리턴된 값을 결과 집합에 반영

 

날짜 처리 함수

 

☞ add_months(date, number)

- 해당 날짜에 지정한 개월 수를 더하거나 뺀 날짜형을 반환

- 말일에서 계산 시 해당 달의 말일을 반환

select
    add_months(sysdate, 1),     -- 오늘부터 한 달 뒤가 언제인지 반환
    add_months(sysdate, -1),    -- 오늘부터 한 달 전이 언제인지 반환
    add_months('22/01/31', 1),  -- 한 달 뒤의 말일을 반환
    add_months('22/02/28', 1)   -- 3/28이 반환되는 것이 아닌 해당 달의 말일이 반환
from
    dual;

 

응용) 사원 테이블에서 입사 후 3개월은 수습 기간이다. 정신 직원이 된 일자는 각 사원 별 조회 (사원명, 입사일, 정직원계약일)

select
    emp_name 사원명,
    hire_date 입사일,
    add_months(hire_date, 3) 정직원계약일
from
    employee;

 

 

☞ extract(단위 from date / timestamp)

- 날짜 정보에서 해당 단위만 숫자형으로 반환

- 시분초는 date가 아닌 timestamp 타입에서만 추출 가능

select
    extract(year from sysdate) "year",
    extract(month from sysdate) "month",
    extract(day from sysdate) "day",
--    extract(hour from sysdate) --오류 발생 (시분초 정보는 timestamp에서)
    extract(hour from cast(sysdate as timestamp)) "hour",   -- 24시제로만 반환, 12시제로 하고 싶다면 /12 나누거나 해야됨..
    extract(minute from cast(sysdate as timestamp)) "minute",
    extract(second from cast(sysdate as timestamp)) "second"
from
    dual;

시/분/초는 sysdate에서 가져올 수 없기 때문에 반드시 timestamp에서 가져와야합니다.

또한, '시'는 24시제로만 반환 되므로 12시제로 하고 싶다면 12를 나누어서 사용해야 합니다.

 

응용) 2001년 입사자만 조회 (사원명, 입사일)

select
    emp_name 사원명,
    hire_date 입사일
from
    employee
where
    extract(year from hire_date) = 2001;

 

 

☞ trunc(date)

- 날짜형에서 시분초 정보를 제거 (시분초를 자정)

select
    to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss'),
    to_char(trunc(sysdate), 'yyyy/mm/dd hh24:mi:ss')
from
    dual;

trunc(sysdate) 에서 시분초 정보가 제거되었기 때문에 두 결과는 완전히 다르게 리턴 되는 것을 확인할 수 있습니다.

 

 

☞ months_between(미래날짜, 과거날짜)

- 두 날짜의 개월 수 차이 반환 (순서유의!!)

select
    round(months_between('23/01/01', sysdate), 1) 개월차
from
    dual;

딱 떨어지지 않아서 round()로 감싸 소수점 처리를 해주었습니다.

순서를 반드시 유의!! 해야합니다. (과거날짜, 미래날짜) 순서가 아니라 (미래날짜, 과거날짜) 순서!

 

응용) 사원 테이블에서 사원의 근무개월수 조회 (사원명, 근무개월수1(n개월), 근무개월수2(x년 y개월))

select
    emp_name 사원명,
    trunc(months_between(sysdate, hire_date)) || '개월' "근무개월1",
    trunc(months_between(sysdate, hire_date) / 12) || '년 ' || trunc(mod(months_between(sysdate, hire_date), 12)) || '개월' "근무개월수2"
from
    employee;

 

 

☞ next_day(date, 요일문자)

- 입력한 날짜 데이터에서 돌아오는 요일의 날짜 반환

select
    sysdate,
    next_day(sysdate, 'mon')
from
    dual;

 

 

☞ last_day(date)

- 해당 날짜가 속한 달의 마지막 날짜 반환

select
    sysdate,
    last_day(sysdate)
from
    dual;