오라클 날짜 함수 정리 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;  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