오라클 날짜 계산, 함수 등
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_DATE(‘MAR 21 05:13′,’MON DD HH24:MI’,’NLS_DATE_LANGUAGE=AMERICAN’) FROM DUAL;
※ 월요일, 화요일 형식이 아닌 월, 화 형식으로 나타내기
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
출처 : http://www.zetswing.com/bbs/board.php?bo_table=ORACLE_TIP&wr_id=20&page=2
—————————————————-
날짜 포멧(Datetime Format Elements
)
| Element | Specify in TO_* datetime functions? | Description |
|---|---|---|
- / , . ; : "text" |
Yes |
Punctuation and quoted text is reproduced in the result. |
AD A.D. |
Yes |
AD indicator with or without periods. |
AM A.M. |
Yes |
Meridian indicator with or without periods. |
BC B.C. |
Yes |
BC indicator with or without periods. |
CC SCC |
No |
Century.
For example, 2002 returns 21; 2000 returns 20. |
D |
Yes |
Day of week (1-7). |
DAY |
Yes |
Name of day, padded with blanks to display width of the widest name of day in the date language used for this element. |
DD |
Yes |
Day of month (1-31). |
DDD |
Yes |
Day of year (1-366). |
DL |
Yes |
Returns a value in the long date format, which is an extension of Oracle Database’s Restriction: You can specify this format only with the |
DS |
Yes |
Returns a value in the short date format. Makes the appearance of the Restriction: You can specify this format only with the |
DY |
Yes |
Abbreviated name of day. |
E |
No |
Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
EE |
No |
Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
FF [1..9] |
Yes |
Fractional seconds; no radix character is printed (use the X format Examples:
|
FM |
Yes |
Returns a value with no leading or trailing blanks. See Also: Additional discussion on this format model modifier in the Oracle Database SQL Reference |
FX |
Yes |
Requires exact matching between the character data and the format model. See Also: Additional discussion on this format model modifier in the Oracle Database SQL Reference |
HH |
Yes |
Hour of day (1-12). |
HH12 |
No |
Hour of day (1-12). |
HH24 |
Yes |
Hour of day (0-23). |
IW |
No |
Week of year (1-52 or 1-53) based on the ISO standard. |
IYY IY I |
No |
Last 3, 2, or 1 digit(s) of ISO year. |
IYYY |
No |
4-digit year based on the ISO standard. |
J |
Yes |
Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers. |
MI |
Yes |
Minute (0-59). |
MM |
Yes |
Month (01-12; January = 01). |
MON |
Yes |
Abbreviated name of month. |
MONTH |
Yes |
Name of month, padded with blanks to display width of the widest name of month in the date language used for this element. |
PM P.M. |
No |
Meridian indicator with or without periods. |
Q |
No |
Quarter of year (1, 2, 3, 4; January – March = 1). |
RM |
Yes |
Roman numeral month (I-XII; January = I). |
RR |
Yes |
Lets you store 20th century dates in the 21st century using only two digits. See Also: Additional discussion on |
RRRR |
Yes |
Round year. Accepts either 4-digit or 2-digit input. If 2-digit, |
SS |
Yes |
Second (0-59). |
SSSSS |
Yes |
Seconds past midnight (0-86399). |
TS |
|
Returns a value in the short time format. Makes the appearance of the Restriction: You can specify this format only with the |
TZD |
Yes |
Daylight savings information. The TZD value is an abbreviated time Example: |
TZH |
Yes |
Time zone hour. (See Example: |
TZM |
Yes |
Time zone minute. (See Example: |
TZR |
Yes |
Time zone region information. The value must be one of the time zone regions supported in the database. Example: US/Pacific |
WW |
No |
Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. |
W |
No |
Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. |
X |
Yes |
Local radix character. Example: |
Y,YYY |
Yes |
Year with comma in this position. |
YEAR SYEAR |
No |
Year, spelled out; |
YYYY SYYYY |
Yes |
4-digit year; |
YYY YY Y |
Yes |
Last 3, 2, or 1 digit(s) of year. |
출처 : http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i165520
최신 댓글