Oracle 날짜함수

날짜함수

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


==================================================================================================


(1) 현재 날자에서 하루를 빼고 싶다고 하면

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

(2) 1시간을 빼고 싶으면
select to_char(sysdate - 1/24,'yyyy/mm/dd hh24:mi:ss') d1 from dual;

(3) 1분을 빼고 싶으면
select to_char(sysdate - 1/24/60,'yyyy/mm/dd hh24:mi:ss') d1 from dual;

(q) 1초를 빼고 싶은면 어떻게 할까요? ^^
select to_char(sysdate - 1/24/60/60,'yyyy/mm/dd hh24:mi:ss') d1 from dual;

select /* 오늘날짜 시분초 포함*/ to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual

select /* 오늘날짜 00시 00분 00초 */ to_char(trunc(sysdate),'yyyy/mm/dd hh24:mi:ss') from dual

select /* 오늘날짜 00시 00분 00초 위와 동일*/ to_char(trunc(sysdate,'dd'),'yyyy/mm/dd hh24:mi:ss') from dual

select /* 이번달 1일 00시 00분 00초 */ to_char(trunc(sysdate,'mon'),'yyyy/mm/dd hh24:mi:ss') from dual

select /* 올해 1월 1일 00시 00분 00초 */ to_char(trunc(sysdate,'year'),'yyyy/mm/dd hh24:mi:ss') from dual

select /* 올해 1월 1일 00시 00분 00초 */ to_char(to_date('2002','yyyy'),'yyyy/mm/dd hh24:mi:ss') from dual

select /* 2월 1일 00시 00분 00초 */ to_char(to_date('200202','yyyymm'),'yyyy/mm/dd hh24:mi:ss') from dual

select /* 2월 2일 00시 00분 00초 */ to_char(to_date('20020202','yyyymmdd'),'yyyy/mm/dd hh24:mi:ss') from dual

select /* 2월 2일 00시 00분 01초 */ to_char(to_date('20020202','yyyymmdd')+1/68400,'yyyy/mm/dd hh24:mi:ss') from dual

select /* 2월 2일 00시 00분 00초 -> 한달뒤*/ to_char