반응형
[펌]오라클 날짜 계산
출처 : http://www.zetswing.com/bbs/board.php?bo_table=ORACLE_TIP&wr_id=20&page=
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
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
