[Oracle PL-SQL 예제] SQL 함수(날짜함수)

[정민이]

실무개발자를위한 실무교육 전문교육센터학원
www.oraclejava.co.kr에 오시면 보다 다양한 강좌를 보실 수 있습니다.

날짜 및 시간을 조작 하는 함수들 이다.

ADD_MONTHS(date, n) : date에 n달을 추가한다. date값을 반환하며 n은 정수이다.
LAST_DAY(date) : date를 포함하는 달의 마지막 날을 반환, date값을 반환 한다.
MONTHS_BETWEEN(date1, date2) : date1과 date2의 차이를 달 수로 표현, 정수를 반환하며 차이가 1개월 미만인 경우는 1보다 작은 소수를 반환한다.  즉 (date1 – date2)를 나타낸 것이라고 생각하면 된다.
NEXT_DAY(date, c) : 날짜 date를 포함해서 이후 나타나는 첫번째 c요일을 반환 한다.
ROUND(date, fmt) : date를 지정한 포맷 형식에 맞춰 표시하는데 반올림 한다.
SYSDATE : 해당 시스템의 현재 날짜 및 시간을 반환
SYSTIMESTAMP : 오라클 9i에서 추가 되었으며 SYSDATE와 마찬가지로 해당 시스템의 현재 날짜 및 시간을 반환 한다. TIMESTAMP는 DATETIME의 확장이며 보다 정교한 시간을 나타낼 수 있다.
TRUNC(date, fmt) : date를 지정한 포맷 형식에 맞춰 표시하는데 절삭 한다.

[ROUND, TRUNC함수의 fmt 문자열]
YYYY , YYY , YY , Y , YEAR : 년도의 첫날(7월1일부터 반올림)
Q : 분기의 첫날(분기의 두번째 달 16일부터 반올림)
MONTH , MON, MM : 월의 첫날(16일부터 반올림)
W : 월의 첫날과 같은 요일
DDD, DD : 일(정오를 지나면 반올림)
DAY, DY, D : 주의 첫 요일(수요일 정오가 지나면 반올림)
HH, HH12, HH24 : 시단위
MI : 분단위

 현재 시스템의 시간 및 날짜를 확인
SQL> select sysdate, systimestamp from dual;

SYSDATE            SYSTIMESTAMP
------------    ---------------------------
03/10/01          03/10/01 10:52:28.243000 +09:00

 “2003년12월1일”에 3개월을 더하면 어떤 결과가 나올까?
SQL> select add_months('03/12/01', 3) from dual;

ADD_MONT
--------
04/03/01

 “2003년10월1일을 포함하는 달의 마지막 날을 반환 하는 예문 이다.
SQL> select last_day('03/10/01') from dual;

LAST_DAY
--------
03/10/31       

SQL> select months_between('04/01/01','05/01/01') from dual;

MONTHS_BETWEEN('04/01/01','05/01/01')
-------------------------------------
-12

SQL> select months_between('04/01/01','05/01/30') from dual;

MONTHS_BETWEEN('04/01/01','05/01/30')
-------------------------------------
-12.935484

 “2003년 10월1일을 포함하여 다음 일요일을 구하는 예문이다.
SQL> select next_day('03/10/01','일요일')from dual;

NEXT_DAY
--------
03/10/05

  SYSDATE를 그냥 출력하면 날짜만 나오게 된다. 만약 시간도 확인을 하기 위해서는 to_char 와 같은 변환 함수를 이용하여 문자로 변환을 시켜 줘야 한다.
SQL> select sysdate, to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') from dual;

SYSDATE  TO_CHAR(SYSDATE,'YY
-------- -------------------
04/02/17 2004.02.17 15:25:53

 현재 날짜를 2004년2월17일 오후15시25분 정도라면 가정 하고 다음 예문을 이해 하자. MONTH는 월의 첫날을 출력하는데 17일 이므로 월은 반올림 하면 다음 월인 3월1일이 출력된다. 또한 DAY의 경우 주의 첫요일을 출력하는데 2월17일은 화요일 이므로 반올림 대상이 아니다. 즉 주의 첫요일인 2월15일이 출력 되는 것이다.

SQL> select round(sysdate, 'MONTH'), round(sysdate, 'DAY') from dual;

ROUND(SY ROUND(SY
-------- --------
04/03/01 04/02/15

 EMP 테이블에서 “SMITH” 사원의 오늘(2004년2월17일 이라고 가정)을 기준으로 입사한지 몇 개월째 인지 파악 하려고 한다.(16째일 부터는 1개월로 반올림하는 예문이다.)

SQL> select ename, hiredate "입사일", sysdate "기준일",
  2        round(months_between(sysdate, hiredate), 0) "입사개월수"
  3  from emp
  4  where ename = 'SMITH';

ENAME      입사일  기준일  입사개월수
---------- -------- -------- ----------
SMITH      80/12/17 04/02/17        278

 아래는 trunc를 사용하는 예문이다. 위의 예문에서는 현재 날짜가 17일 이므로 반올림이 되었지만 절삭을 하면 월의 첫날은 2월1일이 된다.

SQL> select trunc(sysdate, 'MONTH'), trunc(sysdate, 'DAY') from dual;

TRUNC(SY TRUNC(SY
-------- --------
04/02/01 04/02/15

 일단위로 보면 정오를 지났으므로 일자가 반올림 된다.
SQL> select round(sysdate, 'DD') from dual;

ROUND(SY
--------
04/02/18

[날짜 산술 연산]
date + number  date
date – number  date
date – date  정수
date + number/24  date

SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------
04/02/18 06:49:35.008000 +09:00

SQL> select sysdate +60 "오늘부터 60일 후" from dual;
오늘부터
--------
04/04/18

SQL> select systimestamp + 60 "오늘부터 60일 후"  from dual;
오늘부터
--------
04/04/18

 아래 예문은 1970년 1월1일부터 오늘까지의 일수차이를 반환하는 예문이다.

SQL> select trunc(sysdate - to_date('19700101','yyyymmdd')) from dual;

TRUNC(SYSDATE-TO_DATE('19700101','YYYYMMDD'))
---------------------------------------------
                                        12466