2. 날짜 처리함수(datetime function)
2-1) ADD_MONTHS 함수
2-2) CURRENT_DATE 함수
2-3) URRENT_TIMESTAMP 함수
2-4) DBTIMEZONE 함수
2-5) EXTRACT(datetime) 함수
2-6) FROM_TZ 함수
2-7) LAST_DAY 함수
2-8) LOCALTIMESTAMP 함수
2-9) MONTHS_BETWEEN 함수
2-10) NEW_TIME 함수
2-11) NEXT_DAY 함수
2-12) NUMTODSINTERVAL 함수
2-13) NUMTOYMINTERVAL 함수
2-14) ROUND(date) 함수
2-15) SESSIONTIMEZONE 함수
2-16) SYS_EXTRACT_UTC 함수
2-17) SYSDATE 함수
2-18) SYSTIMESTAMP 함수
2-19) TO_DSINTERVAL 함수
2-20) TO_TIMESTAMP 함수
2-21) TO_TIMESTAMP_TZ 함수
2-22) TO_YMINTERVAL 함수
2-23) TRUNC(date) 함수
2-24) TZ_OFFSET 함수
2-1) ADD_MONTHS 함수
2-2) CURRENT_DATE 함수
2-3) URRENT_TIMESTAMP 함수
2-4) DBTIMEZONE 함수
2-5) EXTRACT(datetime) 함수
2-6) FROM_TZ 함수
2-7) LAST_DAY 함수
2-8) LOCALTIMESTAMP 함수
2-9) MONTHS_BETWEEN 함수
2-10) NEW_TIME 함수
2-11) NEXT_DAY 함수
2-12) NUMTODSINTERVAL 함수
2-13) NUMTOYMINTERVAL 함수
2-14) ROUND(date) 함수
2-15) SESSIONTIMEZONE 함수
2-16) SYS_EXTRACT_UTC 함수
2-17) SYSDATE 함수
2-18) SYSTIMESTAMP 함수
2-19) TO_DSINTERVAL 함수
2-20) TO_TIMESTAMP 함수
2-21) TO_TIMESTAMP_TZ 함수
2-22) TO_YMINTERVAL 함수
2-23) TRUNC(date) 함수
2-24) TZ_OFFSET 함수
2-1) ADD_MONTHS 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
ADD_MONTHS(d, n)는 날짜 d에 n 개월을 더한 일자를 반환한다.
【예제】
SQL> select current_date today, add_months(current_date,1) "next month"
2 from dual;
SQL> select current_date today, add_months(current_date,1) "next month"
2 from dual;
TODAY next mont
--------- ---------
29-JUL-04 29-AUG-04
--------- ---------
29-JUL-04 29-AUG-04
2-2) CURRENT_DATE 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
이 함수는 현재 session의 날짜 정보를 반환한다.
【예제】
SQL> select current_date from dual;
【예제】
SQL> select current_date from dual;
CURRENT_D
---------
31-JUL-04
---------
31-JUL-04
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
--------------------------------------------------------------------------
+09:00
--------------------------------------------------------------------------
+09:00
SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select current_date from dual;
CURRENT_DATE
--------------------
31-JUL-2004 09:31:57
--------------------
31-JUL-2004 09:31:57
SQL> alter session set time_zone='-5:0';
Session altered.
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
--------------------------------------------------------------------------
-05:00
--------------------------------------------------------------------------
-05:00
2-3) URRENT_TIMESTAMP 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
이 함수는 현재 session의 날짜와 시간 정보를 반환한다.
current_timestamp는 time zone까지 출력되지만,
localtimestamp는 time zone은 출력되지 않는다.
【예제】
SQL> select current_timestamp, localtimestamp,
2 current_date from dual;
current_timestamp는 time zone까지 출력되지만,
localtimestamp는 time zone은 출력되지 않는다.
【예제】
SQL> select current_timestamp, localtimestamp,
2 current_date from dual;
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
CURRENT_D
---------
04-AUG-04 11.17.40.768776 AM +09:00
04-AUG-04 11.17.40.768776 AM
04-AUG-04
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
CURRENT_D
---------
04-AUG-04 11.17.40.768776 AM +09:00
04-AUG-04 11.17.40.768776 AM
04-AUG-04
2-4) DBTIMEZONE 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
데이터베이스 timezone을 반환한다.
【예제】
SQL> select dbtimezone from dual;
【예제】
SQL> select dbtimezone from dual;
DBTIME
------
-07:00
------
-07:00
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
--------------------------------------------------------------------------
+09:00
--------------------------------------------------------------------------
+09:00
2-5) EXTRACT(datetime) 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
특정 날짜/시간 값이나 날짜 값을 가진 표현식으로부터
원하는 날짜 영역을 추출하여 출력한다.
원하는 날짜 영역을 추출하여 출력한다.
【형식】
EXTRACT ({year|month|day|hour|minute|second|
timezone_hour|timezone_minute|
timezone_region|timezone_abbr}
FROM {datetime_value_expr|interval_value_rxpr})
EXTRACT ({year|month|day|hour|minute|second|
timezone_hour|timezone_minute|
timezone_region|timezone_abbr}
FROM {datetime_value_expr|interval_value_rxpr})
【예제】
SQL> select extract(year from date '2004-8-2') from dual;
SQL> select extract(year from date '2004-8-2') from dual;
EXTRACT(YEARFROMDATE'2004-8-2')
-------------------------------
2004
-------------------------------
2004
2-6) FROM_TZ 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
이 함수는 timestamp 값을 timestamp with time zone 값으로 변환한다.
【형식】
FROM_TZ ( timestamp_value, time_zone_value)
FROM_TZ ( timestamp_value, time_zone_value)
【예제】
SQL> select from_tz(timestamp '2004-8-11 08:00:00','3:00') from dual;
SQL> select from_tz(timestamp '2004-8-11 08:00:00','3:00') from dual;
FROM_TZ(TIMESTAMP'2004-8-1108:00:00','3:00')
--------------------------------------------------------------------------
11-AUG-04 08.00.00.000000000 AM +03:00
--------------------------------------------------------------------------
11-AUG-04 08.00.00.000000000 AM +03:00
2-7) LAST_DAY 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
이 함수는 지정한 달의 마지막 날을 출력한다.
【형식】
LAST_DAY ( date )
LAST_DAY ( date )
【예제】
SQL> select sysdate, last_day(sysdate) "last day",
2 last_day(sysdate)- sysdate "Days Left"
3 from dual;
SQL> select sysdate, last_day(sysdate) "last day",
2 last_day(sysdate)- sysdate "Days Left"
3 from dual;
SYSDATE last day Days Left
--------- --------- ----------
04-AUG-04 31-AUG-04 27
--------- --------- ----------
04-AUG-04 31-AUG-04 27
2-8) LOCALTIMESTAMP 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
이 함수는 timestamp의 현재 날짜와 시각을 출력한다.
current_timestamp는 time zone까지 출력되지만,
localtimestamp는 time zone은 출력되지 않는다.
current_timestamp는 time zone까지 출력되지만,
localtimestamp는 time zone은 출력되지 않는다.
【형식】
localtimestamp [(timestamp_precision)]
localtimestamp [(timestamp_precision)]
【예제】
SQL> select current_timestamp, localtimestamp,
2 current_date from dual;
SQL> select current_timestamp, localtimestamp,
2 current_date from dual;
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
CURRENT_D
---------
04-AUG-04 11.17.40.768776 AM +09:00
04-AUG-04 11.17.40.768776 AM
04-AUG-04
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
CURRENT_D
---------
04-AUG-04 11.17.40.768776 AM +09:00
04-AUG-04 11.17.40.768776 AM
04-AUG-04
【예제】오류가 발생하는 이유를 잘 이해하자.
SQL> CREATE TABLE local_test(col1 TIMESTAMP WITH LOCAL TIME ZONE);
Table created.
SQL> INSERT INTO local_test VALUES
2 (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'));
(TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'))
*
ERROR at line 2:
ORA-01830: date format picture ends before converting entire input string
SQL> INSERT INTO local_test VALUES
2 (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));
1 row created.
SQL> select * from local_test;
COL1
--------------------------------------------------------------------------
04-AUG-04 11.33.58.183398 AM
--------------------------------------------------------------------------
04-AUG-04 11.33.58.183398 AM
2-9) MONTHS_BETWEEN 함수
--------------------------------------------------------------------------------
MONTHS_BETWEEN(date1,date2) 함수는 date1과 date로 나타내는
날짜와 날짜 사이의 개월 수를 출력한다.
날짜와 날짜 사이의 개월 수를 출력한다.
【예제】
SQL> select months_between
2 (to_date('02-02-2004','MM-DD-YYYY'),
3 to_date('01-01-2003','MM-DD-YYYY') ) "Months"
4 FROM dual;
SQL> select months_between
2 (to_date('02-02-2004','MM-DD-YYYY'),
3 to_date('01-01-2003','MM-DD-YYYY') ) "Months"
4 FROM dual;
Months
----------
13.0322581
----------
13.0322581
2-10) NEW_TIME 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
NEW_TIME(date,zone1,zone2) 함수는 date, zone1 시간대를 zone2 시간대로 출력한다.
여기서 사용되는 zone은 다음 중의 하나이다.
여기서 사용되는 zone은 다음 중의 하나이다.
AST,ADT : Atlantic Standard or Daylight Time
BST,BDT : Bering Standard or Daylight Time
CST,CDT : Central Standard or Daylight Time
EST,EDT : Eastern Standard or Daylight Time
GMT : Greenwich Mean Time
HST,HDT : Alaska-Hawaii Standard or Daylight Time
MST,MDT : Mountain Standard or Daylight Time
NST : Newfoundland Standard Time
PST,PDT : Pacific Standard or Daylight Time
YST,YDT : Yukon Standard or Daylight Time
BST,BDT : Bering Standard or Daylight Time
CST,CDT : Central Standard or Daylight Time
EST,EDT : Eastern Standard or Daylight Time
GMT : Greenwich Mean Time
HST,HDT : Alaska-Hawaii Standard or Daylight Time
MST,MDT : Mountain Standard or Daylight Time
NST : Newfoundland Standard Time
PST,PDT : Pacific Standard or Daylight Time
YST,YDT : Yukon Standard or Daylight Time
【예제】
SQL> alter session set nls_date_format =
2 'DD-MON-YYYY HH24:MI:SS';
SQL> alter session set nls_date_format =
2 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select NEW_TIME(TO_DATE(
2 '11-10-04 01:23:33', 'MM-DD-YY HH24:MI:SS'),
3 'AST', 'PST') FROM DUAL;
NEW_TIME(TO_DATE('11
--------------------
09-NOV-2004 21:23:33
--------------------
09-NOV-2004 21:23:33
2-11) NEXT_DAY 함수
--------------------------------------------------------------------------------
NEXT_DAY(date,char) 함수는 date로부터 char로 명시한 가장 최근의 날짜를 출력한다.
【예제】
SQL> select next_day('02-AUG-2004','MONDAY') from dual;
SQL> select next_day('02-AUG-2004','MONDAY') from dual;
NEXT_DAY('02-AUG-200
--------------------
09-AUG-2004 00:00:00
--------------------
09-AUG-2004 00:00:00
2-12) NUMTODSINTERVAL 함수
--------------------------------------------------------------------------------
NUMTODSINTERVAL(n,'char_expr') 함수는 n을 interval day to second로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
‘DAY’
‘HOUR’
‘MINUTE’
‘SECOND’
char_expr은 다음 중의 하나이다.
‘DAY’
‘HOUR’
‘MINUTE’
‘SECOND’
【예제】
SQL> select numtodsinterval(100,'MINUTE') from dual;
SQL> select numtodsinterval(100,'MINUTE') from dual;
NUMTODSINTERVAL(100,'MINUTE')
--------------------------------------------------------------------------
+000000000 01:40:00.000000000
--------------------------------------------------------------------------
+000000000 01:40:00.000000000
【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename, hiredate,
2 numtodsinterval(100,'day')+hiredate from emp;
SQL> connect scott/tiger
Connected.
SQL> select ename, hiredate,
2 numtodsinterval(100,'day')+hiredate from emp;
ENAME HIREDATE NUMTODSIN
---------- --------- ---------
SMITH 17-DEC-80 27-MAR-81
ALLEN 20-FEB-81 31-MAY-81
WARD 22-FEB-81 02-JUN-81
JONES 02-APR-81 11-JUL-81
MARTIN 28-SEP-81 06-JAN-82
BLAKE 01-MAY-81 09-AUG-81
CLARK 09-JUN-81 17-SEP-81
SCOTT 19-APR-87 28-JUL-87
KING 17-NOV-81 25-FEB-82
TURNER 08-SEP-81 17-DEC-81
ADAMS 23-MAY-87 31-AUG-87
JAMES 03-DEC-81 13-MAR-82
FORD 03-DEC-81 13-MAR-82
MILLER 23-JAN-82 03-MAY-82
---------- --------- ---------
SMITH 17-DEC-80 27-MAR-81
ALLEN 20-FEB-81 31-MAY-81
WARD 22-FEB-81 02-JUN-81
JONES 02-APR-81 11-JUL-81
MARTIN 28-SEP-81 06-JAN-82
BLAKE 01-MAY-81 09-AUG-81
CLARK 09-JUN-81 17-SEP-81
SCOTT 19-APR-87 28-JUL-87
KING 17-NOV-81 25-FEB-82
TURNER 08-SEP-81 17-DEC-81
ADAMS 23-MAY-87 31-AUG-87
JAMES 03-DEC-81 13-MAR-82
FORD 03-DEC-81 13-MAR-82
MILLER 23-JAN-82 03-MAY-82
14 rows selected.
2-13) NUMTOYMINTERVAL 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
NUMTOYMINTERVAL(n,'char_expr') 함수는 n을 interval year to month로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
‘YEAR’
‘MONTH’
char_expr은 다음 중의 하나이다.
‘YEAR’
‘MONTH’
【예제】
SQL> select numtoyminterval(30,'month') from dual;
SQL> select numtoyminterval(30,'month') from dual;
NUMTOYMINTERVAL(30,'MONTH')
---------------------------------------------------------------------------
+000000002-06
---------------------------------------------------------------------------
+000000002-06
【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename,hiredate,
2 numtoyminterval(30,'month')+hiredate from emp;
SQL> connect scott/tiger
Connected.
SQL> select ename,hiredate,
2 numtoyminterval(30,'month')+hiredate from emp;
ENAME HIREDATE NUMTOYMIN
---------- --------- ---------
SMITH 17-DEC-80 17-JUN-83
ALLEN 20-FEB-81 20-AUG-83
WARD 22-FEB-81 22-AUG-83
JONES 02-APR-81 02-OCT-83
MARTIN 28-SEP-81 28-MAR-84
BLAKE 01-MAY-81 01-NOV-83
CLARK 09-JUN-81 09-DEC-83
SCOTT 19-APR-87 19-OCT-89
KING 17-NOV-81 17-MAY-84
TURNER 08-SEP-81 08-MAR-84
ADAMS 23-MAY-87 23-NOV-89
JAMES 03-DEC-81 03-JUN-84
FORD 03-DEC-81 03-JUN-84
MILLER 23-JAN-82 23-JUL-84
---------- --------- ---------
SMITH 17-DEC-80 17-JUN-83
ALLEN 20-FEB-81 20-AUG-83
WARD 22-FEB-81 22-AUG-83
JONES 02-APR-81 02-OCT-83
MARTIN 28-SEP-81 28-MAR-84
BLAKE 01-MAY-81 01-NOV-83
CLARK 09-JUN-81 09-DEC-83
SCOTT 19-APR-87 19-OCT-89
KING 17-NOV-81 17-MAY-84
TURNER 08-SEP-81 08-MAR-84
ADAMS 23-MAY-87 23-NOV-89
JAMES 03-DEC-81 03-JUN-84
FORD 03-DEC-81 03-JUN-84
MILLER 23-JAN-82 23-JUL-84
14 rows selected.
2-14) ROUND(date) 함수
--------------------------------------------------------------------------------
이 함수는 날짜를 주어진 형식으로 반올림하는 함수이다.
날짜 형식이 없으면 가장 가까운 날을 출력한다.
날짜 형식이 없으면 가장 가까운 날을 출력한다.
【형식】
ROUND( date [,fmt] )
ROUND( date [,fmt] )
【예제】
SQL> select localtimestamp, round(sysdate,'year') from dual;
SQL> select localtimestamp, round(sysdate,'year') from dual;
LOCALTIMESTAMP
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.26.24.197977 PM
01-JAN-05
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.26.24.197977 PM
01-JAN-05
SQL> select localtimestamp,round(sysdate,'day') from dual;
LOCALTIMESTAMP
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.29.57.839269 PM
08-AUG-04
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.29.57.839269 PM
08-AUG-04
SQL> select localtimestamp,round(sysdate) from dual;
LOCALTIMESTAMP
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.30.11.552050 PM
05-AUG-04
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.30.11.552050 PM
05-AUG-04
2-15) SESSIONTIMEZONE 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
이 함수는 현재 세션의 시간대역을 출력한다.
【예제】
SQL> select sessiontimezone, current_timestamp from dual;
SQL> select sessiontimezone, current_timestamp from dual;
SESSIONTIMEZONE
--------------------------------------------------------------------------
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
+09:00
04-AUG-04 01.37.13.355873 PM +09:00
--------------------------------------------------------------------------
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
+09:00
04-AUG-04 01.37.13.355873 PM +09:00
SQL> select sessiontimezone, tz_offset(sessiontimezone) from dual;
SESSIONTIMEZONE
--------------------------------------------------------------------------
TZ_OFFS
-------
+09:00
+09:00
--------------------------------------------------------------------------
TZ_OFFS
-------
+09:00
+09:00
2-16) SYS_EXTRACT_UTC 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
sys_extract_utc(datetime_with_timezone) 함수는
UTC(coordinated universal time: Greenwich mean time) 시각을 반환한다.
UTC(coordinated universal time: Greenwich mean time) 시각을 반환한다.
【예제】
SQL> select systimestamp, sys_extract_utc(systimestamp) from dual;
SQL> select systimestamp, sys_extract_utc(systimestamp) from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------
SYS_EXTRACT_UTC(SYSTIMESTAMP)
--------------------------------------------------------------------------
06-AUG-04 02.41.39.258976 PM +09:00
06-AUG-04 05.41.39.258976 AM
--------------------------------------------------------------------------
SYS_EXTRACT_UTC(SYSTIMESTAMP)
--------------------------------------------------------------------------
06-AUG-04 02.41.39.258976 PM +09:00
06-AUG-04 05.41.39.258976 AM
2-17) SYSDATE 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
이 함수는 오늘 현재 날짜와 시각을 출력한다.
【예제】
SQL> select sysdate, current_timestamp from dual;
SQL> select sysdate, current_timestamp from dual;
SYSDATE
---------
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
04-AUG-04
04-AUG-04 01.51.39.767156 PM +09:00
---------
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
04-AUG-04
04-AUG-04 01.51.39.767156 PM +09:00
SQL> select to_char
2 (sysdate, 'MM-DD-YYYY HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'MM
-------------------
08-04-2004 13:53:18
-------------------
08-04-2004 13:53:18
2-18) SYSTIMESTAMP 함수
--------------------------------------------------------------------------------
이 함수는 시스템의 날짜를 출력한다.
【예제】
SQL> select sysdate,systimestamp,localtimestamp from dual;
SQL> select sysdate,systimestamp,localtimestamp from dual;
SYSDATE
---------
SYSTIMESTAMP
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
04-AUG-04
04-AUG-04 01.58.06.346528 PM +09:00
04-AUG-04 01.58.06.346552 PM
---------
SYSTIMESTAMP
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
04-AUG-04
04-AUG-04 01.58.06.346528 PM +09:00
04-AUG-04 01.58.06.346552 PM
2-19) TO_DSINTERVAL 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
이 함수는 문자열을 interval day to second 형식으로 변환하는 함수이다.
【형식】
to_dsinterval ( char [ ‘nlsparam’] )
to_dsinterval ( char [ ‘nlsparam’] )
【예제】
SQL> select sysdate,
2 sysdate+to_dsinterval('003 17:00:00') as "3days 17hours later"
3 from dual;
SQL> select sysdate,
2 sysdate+to_dsinterval('003 17:00:00') as "3days 17hours later"
3 from dual;
SYSDATE 3days 17h
--------- ---------
04-AUG-04 08-AUG-04
--------- ---------
04-AUG-04 08-AUG-04
2-20) TO_TIMESTAMP 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
이 함수는 문자열을 timestamp 형식으로 변환하여 출력한다
【형식】
to_timestamp ( char [,fmt ['nlsparam'] ] )
to_timestamp ( char [,fmt ['nlsparam'] ] )
【예제】
SQL> select to_timestamp('2004-8-20 1:30:00', 'YYYY-MM-DD HH:MI:SS')
2 from dual;
SQL> select to_timestamp('2004-8-20 1:30:00', 'YYYY-MM-DD HH:MI:SS')
2 from dual;
TO_TIMESTAMP('2004-8-201:30:00','YYYY-MM-DDHH:MI:SS')
--------------------------------------------------------------------------
20-AUG-04 01.30.00.000000000 AM
--------------------------------------------------------------------------
20-AUG-04 01.30.00.000000000 AM
2-21) TO_TIMESTAMP_TZ 함수
--------------------------------------------------------------------------------
이 함수는 문자열을 timestamp with time zone 형식으로 변환하여 출력한다.
【형식】
to_timestamp_tz ( char [,fmt ['nlsparam'] ] )
to_timestamp_tz ( char [,fmt ['nlsparam'] ] )
【예제】
SQL> select to_timestamp_tz('2004-8-20 1:30:00 -3:00',
2 'YYYY-MM-DD HH:MI:SS TZH:TZM') from dual;
SQL> select to_timestamp_tz('2004-8-20 1:30:00 -3:00',
2 'YYYY-MM-DD HH:MI:SS TZH:TZM') from dual;
TO_TIMESTAMP_TZ('2004-8-201:30:00-3:00','YYYY-MM-DDHH:MI:SSTZH:TZM')
--------------------------------------------------------------------------
20-AUG-04 01.30.00.000000000 AM -03:00
--------------------------------------------------------------------------
20-AUG-04 01.30.00.000000000 AM -03:00
2-22) TO_YMINTERVAL 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
TO_YMINTERVAL ( char ) 함수는 문자열을 interval year to month 형식으로 변환하는 함수이다.
【예제】
SQL> select sysdate,
2 sysdate+to_yminterval('01-03') "15Months later"
3 from dual;
SQL> select sysdate,
2 sysdate+to_yminterval('01-03') "15Months later"
3 from dual;
SYSDATE 15Months
--------- ---------
04-AUG-04 04-NOV-05
--------- ---------
04-AUG-04 04-NOV-05
2-23) TRUNC(date) 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
이 함수는 날짜를 절삭하여 출력한다.
【형식】
TRUNC ( date [.fmt] )
TRUNC ( date [.fmt] )
【예제】
SQL> select trunc(to_date('27-AUG-04','DD-MON-YY'), 'YEAR')
2 from dual;
SQL> select trunc(to_date('27-AUG-04','DD-MON-YY'), 'YEAR')
2 from dual;
TRUNC(TO_
---------
01-JAN-04
---------
01-JAN-04
2-24) TZ_OFFSET 함수
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
이 함수는 time zone의 offset 값을 출력한다.
【형식】
TZ_OFFSET ( {‘time_zone_name’ | SESSIONTIMEZONE | DBTIMEZONE |
‘{+|-} hh:mi’ } )
TZ_OFFSET ( {‘time_zone_name’ | SESSIONTIMEZONE | DBTIMEZONE |
‘{+|-} hh:mi’ } )
【예제】
SQL> select sessiontimezone, tz_offset('ROK') from dual;
SQL> select sessiontimezone, tz_offset('ROK') from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
TZ_OFFS
-------
+09:00
+09:00
---------------------------------------------------------------------------
TZ_OFFS
-------
+09:00
+09:00
