오라클 날짜 계산, 함수 등

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.

  • If the last 2 digits of a 4-digit year are between 01 and 99
    (inclusive), then the century is one greater than the first 2 digits of
    that year.
  • If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.

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 DATE format (the current value of the NLS_DATE_FORMAT parameter). Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'fmDay, Month dd, yyyy'. In the GERMAN_GERMANY locale, it is equivalent to specifying the format ‘fmDay, dd. Month yyyy‘.

Restriction: You can specify this format only with the TS element, separated by white space.

DS

Yes

Returns a value in the short date format. Makes the appearance of the
date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format ‘MM/DD/RRRR‘. In the ENGLISH_UNITED_KINGDOM locale, it is equivalent to specifying the format ‘DD/MM/RRRR‘.

Restriction: You can specify this format only with the TS element, separated by white space.

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
element to add the radix character). Use the numbers 1 to 9 after FF to
specify the number of digits in the fractional second portion of the
datetime value returned. If you do not specify a digit, then Oracle
Database uses the precision specified for the datetime datatype or the
datatype’s default precision.

Examples: 'HH:MI:SS.FF'

SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from dual;

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 RR datetime format element in the Oracle Database SQL Reference

RRRR

Yes

Round year. Accepts either 4-digit or 2-digit input. If 2-digit,
provides the same return as RR. If you do not want this functionality,
then enter the 4-digit year.

SS

Yes

Second (0-59).

SSSSS

Yes

Seconds past midnight (0-86399).

TS

Yes

Returns a value in the short time format. Makes the appearance of the
time components (hour, minutes, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE initialization parameters.

Restriction: You can specify this format only with the DL or DS element, separated by white space.

TZD 

Yes

Daylight savings information. The TZD value is an abbreviated time
zone string with daylight savings information. It must correspond with
the region specified in TZR.

Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).

TZH

Yes

Time zone hour. (See TZM format element.)

Example: 'HH:MI:SS.FFTZH:TZM'.

TZM

Yes

Time zone minute. (See TZH format element.)

Example: 'HH:MI:SS.FFTZH:TZM'.

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: 'HH:MI:SSXFF'.

Y,YYY

Yes

Year with comma in this position.

YEAR
SYEAR

No

Year, spelled out; S prefixes BC dates with a minus sign (-).

YYYY
SYYYY

Yes

4-digit year; S prefixes BC dates with a minus sign.

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

You may also like...

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 항목은 *(으)로 표시합니다