[오라클] 오라클 날짜 관련 함수 및 참고자료


원문 : http://www.zetswing.com/bbs/board.php?bo_table=ORACLE_TIP&wr_id=20&page=2


1. Oracle에서의 날짜 특징

 

*oracle은 세기,년,월,일,시간,분,초의 내부숫자 형식으로 날짜를 저장합니다.

*디폴트 날짜형식은 'DD-MON-YY' 입니다.

*SYSDATE는 현재의 날짜와 시간을 리턴하는 함수입니다.(date타입)

ex : 2007-01-07 오후 10:34:00

*DUAL은 SYSDATE를 보기위해 사용된 dummy table입니다.

 

2.oracle에서의 날짜연산

 

* 날짜에서 숫자(날수)를 빼거나 더하여 날짜 결과를 리턴합니다. 결과는 날짜형식

* 날짜 사이의 일수를 알기 위하여 2개의 날짜를 뺍니다.

* 시간을 24로 나누어 날짜에 더합니다.

날짜 + 숫자 : 날짜 특정한 날로부터 몇일 후의 날짜 계산

날짜 - 숫자 : 날짜 특정한 날로부터 몇일 전의 날짜 계산

날짜 - 날짜 : 숫자 두 날짜 사이의 차이를 숫자로 계산 

 

3.oracle에서의 날짜 컬럼데이타형

 

date 형

 

4. 월과 일을 문자로 출력시 한글로 나오는거 영문으로 나오게 하기

 

오라클 환경 설정에 따라 아래 쿼리를 실행시키면 "7월" 이라고 나올수 있다.

SELECT TO_CHAR(SYSDATE,'mon') FROM DUAL;

 

오라클 환경 설정에 따라 아래 쿼리를 실행시키면 "월요일" 이라고 나올수 있다.

SELECT TO_CHAR(sysdate,'day') FROM DUAL;

 

영문("Jul")으로 출력시키려면 아래 명령으로 환경설정을 변경한다.

ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';

 

※ 월요일, 화요일 형식이 아닌 월, 화 형식으로 나타내기

SELECT TO_CHAR(sysdate,'day') FROM DUAL;

 

5.날짜의 순서결과 데이타형

 

날짜 - 날짜 = 숫자 
숫자 + 날짜 = 날짜 
(날짜 - 날짜) + 날짜 = 날짜 
날짜 + 날짜 = error

 

※ trunc함수를 날짜데이타에 사용하기

 

select sysdate from dual;
--2006-02-08 오전 12:11:05

 

select trunc(sysdate) from dual;
select trunc(sysdate,'dd') from dual;
--단지 시간을 없애고 날짜만 나오게 한다.
--2006-02-08

 

select trunc(sysdate,'d') from dual;
--시간을 없애고 일을 가장최근에 지난 일요일 일로 초기화합니다.(권장)
--2006-02-05

 

select trunc(sysdate,'d')+1  from dual;
--시간을 없애고 일을 가장최근에 지난 월요일 일로 초기화합니다.

select trunc(sysdate,'d')-1  from dual;
--시간을 없애고 일을 가장최근에 지난 토요일 일로 초기화합니다.

 

select trunc(sysdate,'ww') from dual;
--시간을 없애고 일을 가장최근에 지난 일요일 일로 초기화합니다.
--2006-02-05

 

select trunc(sysdate,'mm') from dual;
--시간을 없애고 일을 1로 초기화합니다.
--2006-02-01

 

select trunc(sysdate,'Y') from dual;
select trunc(sysdate,'YY') from dual;
select trunc(sysdate,'YYY') from dual;
select trunc(sysdate,'YYYY') from dual;
--시간을 없애고 년도는 올해 년도와 월과 일을 모두 1 로 변경하여 출력합니다.

ex. 2006-01-01

 

SELECT TO_CHAR(SYSDATE,'YYYYMMDD') FROM DUAL;

SELECT TO_CHAR('20070715') FROM DUAL;

-- 현재 날짜를 YYYYMMDD 형식으로 출력한다.(자주사용)

 

8자리일자와 6자리시간을 문자열로 출력

select

to_char(sysdate, 'yyyymmdd') ,

to_char(sysdate, 'hh24miss')

from dual

 

6.날짜 관련 쿼리 예제

 

해당일이 그달의 몇째주인지 알아내기(w)

SELECT to_char(to_date('20061224', 'yyyymmdd'), 'w') FROM dual;

 

해당년도의 전체 일수 구하기

SELECT to_date('20001231', 'YYYYMMDD') - to_date('20000101', 'YYYYMMDD') from dual  
SELECT TO_CHAR (TO_DATE (:yyyy || '1231'), 'ddd') ilsu FROM DUAL 
-- 위의 쿼리는 년도를 변수로 사용하였다.

 

UPDATE tab1 SET logdate = sysdate, ismodify = 1 WHERE logdate < sysdate-7

--기록된 날짜(LOGDATE)가 현재날짜(SYSDATE)로부터 일주일이 지났으면

--SYSDATE를LOGDATE에 쓰고 날짜가 바뀌었다는 기록을 남기는(ISMODYFY = 1) 쿼리

 

UPDATE tab1 SET logdate = sysdate, ismodify = 1 WHERE logdate < TRUNC(sysdate,'d')

기록된 날짜(LOGDATE)가 일요일이 한번이라도 지났다면, 즉 이번주 일요일부터 토요일간의 기록이라면 그대로 두고 그 이상 오래된 경우 현재날짜(SYSDATE)를 LOGDATE에 남기는 쿼리

 

select ename,job,hiredate from emp where hiredate between '1981-02-20' and '1981-05-01';
--1981년02월20일부터 1985년05월01일까지의 레코드를 검색한다.(꼭옛날날짜에서최근날짜로검색)

 

select ename,(sysdate - hiredate)/7 week from emp;
--sysdate함수로 현재 날짜시간에서 입사날짜(hiredate)를 빼면 일수가나오고 거기서 7을 나누어

--근무한 주수를 알수있습니다.

 

select * from emp where hiredate='1980/12/17';
--날짜 비교는 ''을 이용하여 비교합니다.

 

select months_between(sysdate,hiredate)/12 ,hiredate from emp;
--오늘날짜에서 입사날짜를 빼서 달수를 구한후 12을 나누어 근무한 년수를 구할수있다.

 

select months_between(to_date(20011129,'yyyymmdd'),to_date(20020228,'yyyymmdd')) from dual;

--첫번째 날짜에서 두번째 날짜를 빼서 달수로 구한다.

 

select round(months_between(sysdate,hiredate)/12) ,hiredate from emp;
--소수점이 있는 결과에서 반올림합니다.

select trunc(months_between(sysdate,hiredate)/12) ,hiredate from emp;
--소수점이 있는 결과에서 버림합니다.

 

ADD_MONTHS 함수예제

 

SELECT ADD_MONTHS(HIREDATE,2) FROM EMP;

-- HIREDATE값에 2달를 더하여 출력

 

SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-1), 'YYYYMMDD'),

TO_CHAR(SYSDATE-30, 'HH24MIDD') FROM DUAL;

-- DATE형 현재 날짜시간에서 1달을 뺀후 출력

 

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20060907230000','YYYYMMDDHH24MISS'),

-1),'YYYYMMDDHH24MI') FROM DUAL;

-- CHAR형 현재 날짜시간에서 1달을 뺀후 출력

 

select add_months(to_date('200706'||'01','yyyymmdd'),-1) from dual

-- 20070601에서 한달을 뺍니다.

 

select add_months(hiredate,-2) from emp;
--입사날짜에서 2달을 빼서 출력합니다.

 

select hiredate+100 from emp;
--입사날짜에서 100일을 더합니다.

 

select hiredate-100 from emp;
--입사날짜에서 100일을 뺍니다.

 

LAST_DAY() 함수

해당 날짜에 달에 마지막 일의 날짜를 출력한다.

사용예제

SELECT LAST_DAY('2006-05-05') FROM DUAL;
--2006-05-31

SELECT LAST_DAY(SYSDATE) FROM DUAL;
--2006-05-31 오후 10:35:51

 

※oracle에서는 날짜함수에(sysdate) 산술연산이 가능합니다.

1일->1

1시간->1/24

1분->1/24/60

1초->1/24/60/60

 

select sysdate-1 from dual;
--지금 시간 기준으로 1일전

 

select sysdate-(1/24) from dual;
--지금 시간 기준으로 1시간전

 

select sysdate+1/24/60*1 from dual;
--지금 시간 기주으로 1분전

 

select sysdate+1/24/60*10 from dual;
--지금 시간 기주으로 10분전

 

select to_date(200611210800,'yyyymmdd hh24miss')+ 10/24 from duaL;
--10시간을 더한다.

 

select to_char(to_date('2005-05-05'),'d') from account;
--날짜를 숫자형식의 요일로 출력(1-일요일,7-토요일)

select to_char(to_date('2005-05-05'),'day') from account;
--날짜를 알파벳요일로 출력

select to_char(to_date('2005-05-05'),'year') from account;
--날짜를 알파벳년도로 출력

 

select to_char(to_date('2005-05-05'),'month') from account;
-- 월을 영문으로 완벽하게 출력

 

select to_char(to_date('2005-05-05'),'mon') from account;
-- 월을 영문 앞 3글자만 출력

 

select  decode(to_char(to_date('2005-05-05'),'d'),
              '2','1',
              '3','2',
              '4','3',
              '5','4',
              '6','5',
              '7','6',
              '1','7') "요일"
from   dual;

--날짜의 요일을 숫자로 출력(1-월요일,7-일요일)

 

DATE형 컬럼 비교시

 

SELECT * FROM TABLE_NAME WHERE FDATE < to_date('20070711','YYYYMMDD')

 

6. 프로그래밍 언어에서 날짜 검색시 방법

 

날짜 관련 컬럼은 DATE, CHAR(8), NCHAR(8)을 주지만 DATE는 7바이트이다.

 

DATE형은 아래와 같이 검색 조건을 사용한다.

 

WHERE A_DATE BETWEEN '2005-10-10' AND '2005-10-30';

WHERE A_DATE BETWEEN TO_DATE('2005-10-10') AND TO_DATE('2005-10-30');

 

CHAR(8), NCHAR(8)형은 아래와 같이 검색조건을 사용한다.

 

WHERE A_DATE BETWEEN '20051010' AND '20051030';

 

두가지의 장단점을 알아보자

 

7. 해당 시간이 현재 24시간이 지났는지 알수 있는 쿼리

 

SELECT CASE WHEN SYSDATE - TO_DATE('20070727','YYYYMMDD') >= 1

THEN 'Y' ELSE 'N' END RESUAL FROM DUAL;

※ SYSDATE가 날짜형이므로 빼주는 값도 날짜형이어야 합니다.

 

SELECT round(to_date('95/05/25'),'DAY') 
FROM dual
1995/05/28 00:00:00

SELECT TRUNC(TO_DATE('95/05/25'), 'DAY') 
FROM dual
1995/05/21 00:00:00

 

문제는 day 함수에 있습니다. 
day함수는 요일을 나타내죠. 
따라서 to_date('95/05/25')를 day로 표시하면 수요일이 나옵니다. 
위에 쿼리는 그걸 반올림하였으니 그 주에 가장 큰 28일이 나왔구요, 
아래 쿼리는 그걸 잘라내버렸으니 그 주에 가장 작은 21일이 나온 겁니다.

 

SELECT SYSDATE +  2/24 FROM DUAL;

-- 현재시간의 2시간후에 시간을 출력

SELECT SYSDATE -  2/24 FROM DUAL;

-- 현재시간의 2시간전의 시간을 출력

 

select  to_char(trunc(sysdate), 'rrrr/mm/dd') A from dual;
select  to_char(trunc(sysdate), 'yyyy/mm/dd') A from dual;

YYYY포맺은 현재를 기준으로 합니다.

RRRR기준은 .년도의 뒷자리를 기준으로
2000년도 기준으로 보면
0-49 년은 after year 35/12/12 ->2055/12/12
50-99 년은 before year 51/12/12 ->1951/12/12
가 됨니다.

 

8. 날짜 관련 함수

 

SYSDATE 함수

? 현재 시스템 날짜를 출력

SELECT SYSDATE FROM DUAL;

-- ORACLE 10g XE 에서 출력되는 날짜형식

-- 2008-05-17 오후 5:15:17

 

LAST_DAY 함수

? 해당 날짜의 달에서 마지막 일을 출력

SELECT LAST_DAY(SYSDATE) FROM DUAL;

--2008-05-31 오후 5:16:54

저작자 표시 비영리 변경 금지
신고