2013년 1월 30일 수요일

오라클 날짜 함수 정리 ORACLE

출처 : http://sixthgo.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-%EB%82%A0%EC%A7%9C-%ED%95%A8%EC%88%98-%EC%A0%95%EB%A6%AC

날짜함수 

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; 





마) 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

댓글 없음:

댓글 쓰기