오라클의 날짜 함수

02.Oracle/DataBase

오라클의 날짜 함수

1010 2009. 1. 22. 17:38

1. 날짜함수

1)오라클의 날짜 형식

우선은 날짜 함수를 들어가기전에 오라클의 날짜 형식이 어떻게 구성되나 알아보도록 하겠습니다.
오라클은 날짜를 아래와 같이 내부적인 숫자 형식으로 관리됩니다.

Centry.year.month.day.hours.minutes.seconds

기본적으로 입력되는 형식 및 display 형식은 ''''DD-MON-YY'''' 이며,
유효날짜는 January 1,4712 B.C ~ December 31,4712 A.D 사이입니다.
머 여러분들이 사용하시기엔 무리없는 (^^;) 날짜겠져..

또한 오라클에서는 현재 날짜와 시간을 리턴해주는 날짜 함수가 있습니다.
아마 앞으로도 자주 사용하시겠지만, SYSDATE 란 함수입니다.
이함수를 DUAL (저번 강의때 설명했던걸로 기억나는데요, 잘 모르시는 분은 이전강의를 참고해주세요..^^)
을 사용하면 현재의 날짜를 확인하실 수 있습니다.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
--------
03/11/27

2) 날짜와 숫자의 계산
위에서 날짜는 숫자 형식으로 관리 된다고 했죠? 그래서 날짜와 날짜끼리
날짜와 숫자끼리의 연산이 가능합니다.
그 연산된 결과는 아래와 같습니다.

2-1) 날짜 - 날짜 = 숫자
   날짜에서 날짜를 빼면 그 날짜사이의 일 수가 나옵니다.
   오랜만에 emp table을 함 예로들어볼께요?
   emp Table의 입사일을 기준으로 사람들이 입사한 기간이 얼마나 되나 함 알아볼까요?

   SQL> SELECT ENAME, HIREDATE, SYSDATE - HIREDATE
   2 FROM EMP;

   ENAME    HIREDATE SYSDATE-HIREDATE
   ---------- -------- ----------------
   SMITH    80/12/17 8380.01446
   ALLEN    81/02/20 8315.01446
   WARD 81/02/22 8313.01446
   JONES    81/04/02 8274.01446
   MARTIN 81/09/28 8095.01446
   BLAKE    81/05/01 8245.01446
   CLARK    81/06/09 8206.01446
   SCOTT    87/04/19 6066.01446
   KING 81/11/17 8045.01446
   TURNER 81/09/08 8115.01446
   ADAMS    87/05/23 6032.01446
   JAMES    81/12/03 8029.01446
   FORD 81/12/03 8029.01446
   MILLER 82/01/23 7978.01446

   위의 결과에서와 같이 SMITH와 같은 경우는 입사한지 현재일 기준으로 8380일이 지났음을 알수 있습니다.

2-2) 날짜 + 날짜 => 에러 발생
   날짜와 날짜끼리는 더하기가 안됩니다.

   SQL> SELECT ENAME, HIREDATE, SYSDATE + HIREDATE
   2 FROM EMP;
   SELECT ENAME, HIREDATE, SYSDATE + HIREDATE
   *
   1행에 오류:
   ORA-00975: 날짜와 날짜의 가산은 할 수 없습니다

2-3) 날짜 + 숫자 = 날짜
   날짜에 숫자를 더하면 해당 날짜에 숫자만큼의 기간이 더해집니다. (2003/11/27일에 테스트한것입니다.)

   SQL> SELECT SYSDATE + 2 FROM DUAL;

   SYSDATE+2
   --------
   03/11/29

2-4) 날짜 - 숫자 = 날짜
   날짜에 숫자를 빼면 해당 숫자만큼의 기간이 빼져서 나오게 됩니다.
   SQL> SELECT SYSDATE + 2 FROM DUAL;

   SYSDATE-2
   --------
   03/11/25

그러면 본격적으로 날짜 함수에 대해서 들어가 볼까요?

2) 날짜 함수
가) ADD_MONTHS : 주어진 일자에 개월 단위의 가감을 원할때 사용합니다.

EMP 테이블에서 SIMTH 직원의 입사일을 가지고 예를 들어보겠습니다.
입사일을 두달씩 더하고 빼보겠습니다.

1 SELECT ENAME, HIREDATE,
2 ADD_MONTHS(HIREDATE, 2),
3 ADD_MONTHS(HIREDATE, -2)
4 FROM
5 EMP
6 WHERE
7*    ENAME = ''''SMITH''''
SQL> /

ENAME    HIREDATE ADD_MONT ADD_MONT
---------- -------- -------- --------
SMITH    80/12/17 81/02/17 80/10/17

결과를 보시면 알지만 SMITH 직원의 입사일은 80년 12월 17일 이지만 ADD_MONTHS 함수를 이용하여 2달을 더하면
81년 2월 17일 두달을 빼면 80년 10월 17일이 나옴을 확인할 수 있습니다.

나) MONTHS_BETWEEN : 주어진 두개의 일자 간격이 몇 개월인지를 보여줍니다.

EMP 테이블에서 입사일자가 현재 일자로부터 몇개월이 됐는지 알아보겠습니다.

   SQL> SELECT ENAME, HIREDATE, SYSDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE)
   2 FROM EMP;

   ENAME    HIREDATE SYSDATE MONTHS_BETWEEN(SYSDATE,HIREDATE)
   ---------- -------- -------- --------------------------------
   SMITH    80/12/17 03/12/10 275.806019
   ALLEN    81/02/20 03/12/10 273.709244
   WARD 81/02/22 03/12/10 273.644728
   JONES    81/04/02 03/12/10 272.289889
   MARTIN 81/09/28 03/12/10    266.45118
   BLAKE    81/05/01 03/12/10 271.322148
   CLARK    81/06/09 03/12/10 270.064083
   SCOTT    87/04/19 03/12/10 199.741502
   KING 81/11/17 03/12/10 264.806019
   TURNER 81/09/08 03/12/10 267.096341
   ADAMS    87/05/23 03/12/10    198.61247

   ENAME    HIREDATE SYSDATE MONTHS_BETWEEN(SYSDATE,HIREDATE)
   ---------- -------- -------- --------------------------------
   JAMES    81/12/03 03/12/10 264.257631
   FORD 81/12/03 03/12/10 264.257631
   MILLER 82/01/23 03/12/10    262.61247

다) LAST_DAY : 주어진 일자가 포함된 월의 말일을 알수 있습니다.

현재날짜를 (2003-12-10) 통해서 월말이 어떻게 되는지 확인해보도록 하겠습니다.

SQL> SELECT SYSDATE, LAST_DAY(SYSDATE) FROM DUAL;

SYSDATE LAST_DAY
-------- --------
03/12/10 03/12/31

말일이 12월 31일임을 알 수 있습니다.

라) SYSTIMESTAMP : DATABASE의 TIMEZONE을 포함한 시스템 날짜를 보여준다.

SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
---------------------------------------------------------------------------
03/12/11 00:07:32.000001 +09:00

바) NEXT_DAY

SYNTAX : NEXT_DAY (DATE, CHAR)

DATE : 일자
CHAR : 요일을 말합니다.(월/화/수/목/금/토/일) 위에 월요일 이렇게 해도 상관없습니다.
이것은 DATABASE의 LANGUAGE 와 관련있습니다.
지금 제가 테스트 하는 것은 한글로 셋팅되어 있기 때문에 가능한거구여...
영문으로 셋팅되어 있으면.. MONDAY,TUESDAY.... 머 이렇게 되어야겠져..^^

NEXT_DAY는 입력한 DATE(일자) 다음 주 입력한 요일의 일자를 보여줍니다.

아래의 예를 보면 12월 10일 다음주 목요일의 날짜를 보여주는 것입니다.

설명하려니 좀 어렵네여..ㅋㅋ 아래의 예를 보면 쉽게 이해되실듯..ㅋㅋ

SQL> SELECT NEXT_DAY(SYSDATE,''''목요일'''') FROM DUAL;

NEXT_DAY
--------
03/12/18

2. 변환함수
: 오라클에서는 문자와 숫자, 문자와 날짜간의 데이터형 변환을 위해 함수를 제공합니다.
   숫자나 날짜가 문자로 변환되는데 필요한 함수가 TO_CHAR 이며,
   문자가 숫자로 변형될때는 TO_NUMBER,
   문자가 일자로 변형될 때 TO_DATE를 사용합니다.
   그럼 예제를 통하여 자세히 알아보겠습니다.

   1) TO_CHAR : 날짜를 문자로 바꾸어줍니다.

SYNTAX : TO_CHAR (DATE, FORMAT)

FORMAT은 DATE형을 문자로 바꿀때 쓰이는 변환 형식을 말합니다.
자주쓰이는 것에 대해서 예와 함께 설명하도록 하겠습니다.

가) YYYY 또는 SYYYY : 년도, S를 지정하면 기원전 년도에 - 가 붙음.

SQL> SELECT TO_CHAR(SYSDATE, ''''YYYY''''), TO_CHAR(SYSDATE, ''''SYYYY'''')
2 FROM DUAL;

TO_CHAR(SYSDATE, ''''YYYY'''') TO_CHAR(SYSDATE, ''''SYYYY'''')
------------------------ ---------------------------
2003 2003

나) YYY, YY, Y : 년의 아래 3자리, 2자리, 1자리를 나타냅니다.
SQL> SELECT TO_CHAR(SYSDATE, ''''YYY''''), TO_CHAR(SYSDATE, ''''YY''''), TO_CHAR(SYSDATE, ''''Y'''')
2 FROM DUAL;

TO_ TO T
--- -- -
003 03 3

다) SYEAR, YEAR : 년도를 철자로 나타냅니다.

SQL> SELECT TO_CHAR(SYSDATE, ''''SYEAR''''), TO_CHAR(SYSDATE, ''''YEAR'''') FROM DUAL;

TO_CHAR(SYSDATE,''''SYEAR'''') TO_CHAR(SYSDATE,''''YEAR'''')
--------------------------------- ---------------------------------
TWO THOUSAND THREE TWO THOUSAND THREE

라) Q : 입력일자의 분기를 나타냅니다.
SQL> SELECT TO_CHAR(SYSDATE, ''''Q'''') FROM DUAL;

T
-
4

마) MM : 월을 나타냅니다.
SQL> SELECT TO_CHAR(SYSDATE, ''''MM'''') FROM DUAL;

TO
--
12

바) MONTH, MON : 월의 명칭, 3문자의 단축형을 나타냅니다.
SQL> SELECT TO_CHAR(SYSDATE, ''''MONTH''''), TO_CHAR(SYSDATE, ''''MON'''') FROM DUAL;

TO_CHAR(S TO_
--------- ---
DECEMBER DEC

사) DDD, DD, D : 년, 월, 주의 몇번째 날인가를 보여줌

SQL> SELECT TO_CHAR(SYSDATE, ''''DDD''''), TO_CHAR(SYSDATE, ''''DD''''), TO_CHAR(SYSDATE, ''''D'''') FROM DUAL;

TO_ TO T
--- -- -
345 11 5

아) DAY, DY : 요일 또는 3문자의 단축형

SQL> SELECT TO_CHAR(SYSDATE, ''''DAY''''), TO_CHAR(SYSDATE, ''''DY'''') FROM DUAL;

TO_CHA TO
------ --
목요일 목

자) AM, PM : 오전 / 오후를 표시한다.

SQL> SELECT TO_CHAR(SYSDATE, ''''AM''''), TO_CHAR(SYSDATE, ''''PM'''') FROM DUAL;

TO_C TO_C
---- ----
오전 오전

차) HH, HH12 : 시각 (1-12)

SQL> SELECT TO_CHAR(SYSDATE, ''''HH''''), TO_CHAR(SYSDATE, ''''HH12'''') FROM DUAL;

TO TO
-- --
12 12

카) HH24 : 시각 (0-23)

SQL> SELECT TO_CHAR(SYSDATE, ''''HH24'''') FROM DUAL;

TO
--
00

타) MI : 분

SQL> SELECT TO_CHAR(SYSDATE, ''''MI'''') FROM DUAL;

TO
--
34

하) SS : 초

SQL> SELECT TO_CHAR(SYSDATE, ''''SS'''') FROM DUAL;

TO
--
57

물론 위의 포멧을 적절히 조합시킬 수 있습니다.
현재 시스템 날짜의 년월일 시분초 까지 한번 나타내도록 하겠습니다.

SQL> SELECT TO_CHAR(SYSDATE, ''''YYYY/MM/DD HH24:MI:SS'''') FROM DUAL;

TO_CHAR(SYSDATE,''''YY
-------------------
2003/12/11 00:36:27

2) TO_CHAR (NUMBER, FORMAT) : 숫자를 문자로 변환할때도 TO_CHAR 함수를 사용합니다.
숫자용 포멧은 아래와 같습니다.

가) 9 : 숫자 출력
나) 0 : 숫자 앞에 0 표시
다) . : 소수점 자리 표시
라) , : 지정한 위치에 , 표시
마) $ : 달러 표시
바) L : 국가별 화폐단위표시
사) MI : 오른쪽에 마이너스 표시

그럼 위의 포멧을 적용한 예를 살펴보겠습니다.

   SQL> SELECT
   2 TO_CHAR(1234,''''09,999''''),
   3 TO_CHAR(1234.56,''''99,999.99''''),
   4 TO_CHAR(1234,''''$99,999''''),
   5 TO_CHAR(1234,''''L99,999''''),
   6 TO_CHAR(-1234,''''99,999MI'''')
   7 FROM DUAL;

   TO_CHAR TO_CHAR(12 TO_CHAR( TO_CHAR(1234,''''L99 TO_CHAR
   ------- ---------- -------- ----------------- -------
01,234 1,234.56 $1,234 ￦1,234 1,234-

3) TO_NUMBER(CHAR, FORMAT) : 숫자로 변환가능한 문자열을 숫자로 변환시켜줍니다.
FORMAT은 2) TO_CHAR (NUMBER, FORMAT)에서 사용했던 FORMART을 사용합니다.

SQL> SELECT
2 TO_NUMBER(''''1234''''),
3 TO_NUMBER(''''123,123'''',''''999,999'''')
4 FROM DUAL;

TO_NUMBER(''''1234'''') TO_NUMBER(''''123,123'''',''''999,999'''')
----------------- ------------------------------
1234 123123
4) TO_DATE(CHAR, FROMAT) : 문자를 날짜로 변환시켜주며, DATE형의 문자형 변환시와 반대지만 사용형식은 동일합니다.

SQL> SELECT TO_DATE(''''2003/12/11 00:36:27'''', ''''YYYY/MM/DD HH24:MI:SS'''') FROM DUAL;

TO_DATE(
--------
03/12/11