Oracle Date Function 講解和事例

myhuaer發表於2005-11-10
OracleFunction
請大家記住

1 year=1*12 months
1 day=24 hours=24*(1*60mins)=24*60*(1*60 seconds)
1 week =7 days
注意：黑色字型是 oracle 8i，9i都可以使用的函式，藍色字型是 Oracle9i新增的部分。
請大家在使用時，注意版本限制。
add_months
current_date
current_timestamp
datimezone
extract(datetime)
last_day
months_between
new_time
next_day
round
SYSDATE
systimestamp
TO_DATE
trunc()

1： Add_Months
語法： Add_Months(d,n) 函式 -- n可正可負 作用： Add_Months(d,n) 返回日期 D 加上n 月的日期
Example:
A：下個月的今天
select add_months(sysdate,1) from dual --在月份上增加

B：上個月的今天
select add_months(sysdate,-1) from dual --在月份上減少

C: 上個月的最後一天
select last_day(add_months(sysdate,-1)) from dual
2: Current_date
語法：
Current_date
作用：
CURRENT_DATE 返回當前Session 時區的當前日期。
Example：
A：不特別設定 oracle time_zone 是和系統Time_zone 一致。
select current_date,sysdate from dual
SQL> select current_date,sysdate from dual ;
CURRENT_DATE SYSDATE
--------------- ---------------
20051109 160535 20051109 160534 B：
ALTER SESSION SET TIME_ZONE = '-5:0'; SQL> select current_date,sysdate from dual;
CURRENT_DATE SYSDATE
--------------- ---------------
20051109 030644 20051109 160644
3: Current_timestamp
語法：
CURRENT_TIMESTAMP（precision)
作用：
current_timestamp 返回此session 所在時區的當前日期和時間。
資料型別是 TIMESTAMP WITH TIME ZONE.
Example:
A：default timestamp
SQL> select CURRENT_TIMESTAMP from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
09-NOV-05 04.27.41.416811 PM +08:00
B: use precision
SQL> ALTER SESSION SET TIME_ZONE ='-5:0';
Session altered.
SQL> SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL
SESSIONTIMEZONE CURRENT_TIMESTAMP
-------------------- --------------------------------------------------
-05:00 09-NOV-05 03.33.32.595277 AM -05:00
C: You can user others date funtion on current_timestamp
SQL> select to_char(current_timestamp,'YYYYMMDD') from dual;
TO_CHAR(
--------
20051109
4: Dbtimezone
作用：
Exreact 提取並且返回日期時間或時間間隔表示式中特定的時間域。
Example:
A：
SELECT EXTRACT(YEAR FROM sysdate ) FROM DUAL
SELECT EXTRACT(YEAR FROM to_date('20051110','YYYYMMDD')) FROM DUAL
EXTRACT(YEARFROMTO_DATE('20051110','YYYYMMDD'))
-----------------------------------------------
2005
B：
SQL> SELECT EXTRACT(month FROM to_date('20051110','YYYYMMDD')) FROM DUAL;
EXTRACT(MONTHFROMTO_DATE('20051110','YYYYMMDD'))
------------------------------------------------
116: Last_day
語法：
last_day(date)
作用：
last_day 返回指定日期月的最後一天
Example：
SQL> SELECT SYSDATE,LAST_DAY(SYSDATE) "Last",LAST_DAY(SYSDATE) - SYSDATE "Days Left" FROM DUAL;
SYSDATE Last Days Left
--------- --------- ----------
10-NOV-05 30-NOV-05 20
7：Months_between
語法：
Months_between(date1,date2) ---(date1-date2)
作用：
months_between 返回兩個日期間相差多少個月(Oracle default 認為31 天/month）。
Example：
A： select months_between(sysdate-3,sysdate) "相差月數",months_between(sysdate-3,sysdate)*31 "相差天數" from dual
相差月數 相差天數
---------- ----------
-.09677419 -3 （11 月只有30 天）
8: New_time
語法：
NEW_TIME ( date , zone1 , zone2 )
作用：
New_time 返回在日期和時間在時區1所對應的時區2的日期和時間。
（使用此函式Oracle paremeters NLS_DATE_FORMAT 必須是24 小時制）
引數：
Zone1 和 Zone2 引數列表
1 AST, ADT: Atlantic Standard or Daylight Time
2 BST, BDT: Bering Standard or Daylight Time
3 CST, CDT: Central Standard or Daylight Time
4 EST, EDT: Eastern Standard or Daylight Time
5 GMT: Greenwich Mean Time
6 HST, HDT: Alaska-Hawaii Standard Time or Daylight Time.
7 MST, MDT: Mountain Standard or Daylight Time
8 NST: Newfoundland Standard Time
9 PST, PDT: Pacific Standard or Daylight Time
10 YST, YDT: Yukon Standard or Daylight Time
Example：
SQL> SELECT NEW_TIME(sysdate,'AST','PST') "New Date and Time",sysdate FROM DUAL;
New Date and Time SYSDATE
------------------- -------------------
2005-11-10 06:24:06 2005-11-10 10:24:06
9： Next_day
語法:
NEXT_DAY ( date , char ) --根據你的 NLS_DATE_LANGUAGE 不同而不同。
作用：
返回從date 開始下一個 Char所對應的星期幾的日期。
Example:
A: 下一個星期一 （從sysdate 開始的下一個星期一的日期）
SELECT next_day(SYSDATE,'星期一') FROM dual
NEXT_DAY(SYSDATE,'星期一')
--------------------------
05-11-14 10:48:01
B：如果你的 NLS_DATE_LANGUAGE 是 Spanish
NEXT_DAY ('01-JAN-1997', 'LUNES') ==> 06-JAN-1997
10: ROUND
語法：
ROUND (date IN DATE [, format_mask VARCHAR2])
作用：
Round 返回date 所對應格式Format_mask的日期。如果format_mask 省略則返回最近一天的日期。
10.1Round 和Trunc 函式的格式

Format Mask

Rounds or Truncates to

CC or SSC

Century

SYYY, YYYY, YEAR, SYEAR, YYY, YY, or Y

Year (rounds up to next year on July 1)

IYYY, IYY, IY, or I

Standard ISO year

Q

Quarter (rounds up on the sixteenth day of the second month of the quarter)

MONTH, MON, MM, or RM

Month (rounds up on the sixteenth day, which is not necessarily the same as the middle of the month)

WW

Same day of the week as the first day of the year

IW

Same day of the week as the first day of the ISO year

W

Same day of the week as the first day of the month

DDD, DD, or J

Day

DAY, DY, or D

Starting day of the week

HH, HH12, HH24

Hour

MI

Minute

Example:

  • Round up to the next century:

    TO_CHAR (ROUND (TO_DATE ('01-MAR-1994'), 'CC'), 'DD-MON-YYYY')
==> 01-JAN-2000

  • Round back to the beginning of the current century:

    TO_CHAR (ROUND (TO_DATE ('01-MAR-1945'), 'CC'), 'DD-MON-YYYY')
==> 01-JAN-1900

  • Round down and up to the first of the year:

    ROUND (TO_DATE ('01-MAR-1994'), 'YYYY') ==> 01-JAN-1994
ROUND (TO_DATE ('01-SEP-1994'), 'YEAR') ==> 01-JAN-1995

  • Round up and down to the quarter (first date in the quarter): 

    ROUND (TO_DATE ('01-MAR-1994'), 'Q') ==> 01-APR-1994
ROUND (TO_DATE ('15-APR-1994'), 'Q') ==> 01-APR-1994

  • Round down and up to the first of the month:

    ROUND (TO_DATE ('12-MAR-1994'), 'MONTH') ==> 01-MAR-1994
ROUND (TO_DATE ('17-MAR-1994'), 'MM') ==> 01-APR-1994

  • Day of first of year is Saturday:

    TO_CHAR (TO_DATE ('01-JAN-1994'), 'DAY') ==> 'SATURDAY'

    So round to date of nearest Saturday for `01-MAR-1994':

    ROUND (TO_DATE ('01-MAR-1994'), 'WW') ==> 26-FEB-1994

  • First day in the month is a Friday:

    TO_CHAR (TO_DATE ('01-APR-1994'), 'DAY') ==> FRIDAY

    So round to date of nearest Friday from April 16, 1994:

    TO_CHAR ('16-APR-1994'), 'DAY') ==> SATURDAY
ROUND (TO_DATE ('16-APR-1994'), 'W') ==> 15-APR-1994
TO_CHAR (ROUND (TO_DATE ('16-APR-1994'), 'W'), 'DAY') ==> FRIDAY

使用To_char 和Round 組合顯示日期：

  • Round back to nearest day (time always midnight):

    TO_CHAR (ROUND (TO_DATE ('11-SEP-1994 10:00 AM',
                      'DD-MON-YY HH:MI AM'), 'DD'),
         'DD-MON-YY HH:MI AM')
==> 11-SEP-1994 12:00 AM

  • Round forward to the nearest day:

    TO_CHAR (ROUND (TO_DATE ('11-SEP-1994 4:00 PM',
                      'DD-MON-YY HH:MI AM'), 'DD'),
         'DD-MON-YY HH:MI AM')
==> 12-SEP-1994 12:00 AM

  • Round back to the nearest hour:

    TO_CHAR (ROUND (TO_DATE ('11-SEP-1994 4:17 PM',
                      'DD-MON-YY HH:MI AM'), 'HH'),
         'DD-MON-YY HH:MI AM')
==> 11-SEP-1994 04:00 PM
11 TO_DATE
語法：
TO_DATE ( char, fmt, ’ nlsparam ’)
作用：
To_date 將char,varchar2,nchar或者nvarhcar2 的資料型別的字串轉換為日期型別。
Fmt 表示特定格式的日期型別。
Example:
SELECT TO_DATE(’January 15, 1989, 11:00 A.M.’,’Month dd, YYYY, HH:MI A.M.’,’NLS_DATE_LANGUAGE = American’)
FROM DUAL;
12：Trunc
語法：
TRUNC ( date，fmt)
作用：
Trunc 函式返回以特定格式(fmt) 截去某一部分的日期。
(Trunc 函式的日期顯示格式同ROUND 函式請參考Chart 10.1)
Example：

本月的第一天
select trunc(sysdate,'month') from dual
本年的第一天
select trunc(sysdate,'year') from dual

本月的最後一天
select last_day(sysdate) from dual
本週的第一天
select trunc(sysdate,'day') from dual --Oracle default start week is Sunday
select trunc(sysdate,'iw') from dual ---ISO year default start week is Monday
本週的星期一
select trunc(sysdate,'day')+1 from dual --2，3，4，5，6，
select trunc(sysdate,'iw')+1 from dual --2，3，4，5，6，

經常會用到的一些Trunc 函式例子（預設的日期格式是DD-MON-YYYY);

  • Without a format mask, TRUNC sets the time to 12:00 A.M. of the same day: 

    TO_CHAR (TRUNC (TO_DATE ('11-SEP-1994 9:36 AM', 'DD-MON-YYYY HH:MI AM'))
==> 11-SEP-1994 12:00 AM

  • Trunc to the beginning of the century in all cases:

    TO_CHAR (TRUNC (TO_DATE ('01-MAR-1994'), 'CC'), 'DD-MON-YYYY')
==> 01-JAN-1900

TO_CHAR (TRUNC (TO_DATE ('01-MAR-1945'), 'CC'), 'DD-MON-YYYY')
==> 01-JAN-1900

  • Trunc to the first of the current year:

    TRUNC (TO_DATE ('01-MAR-1994'), 'YYYY') ==> 01-JAN-1994
TRUNC (TO_DATE ('01-SEP-1994'), 'YEAR') ==> 01-JAN-1994

  • Trunc to the first day of the quarter: 

    TRUNC (TO_DATE ('01-MAR-1994'), 'Q') ==> 01-JAN-1994
TRUNC (TO_DATE ('15-APR-1994'), 'Q') ==> 01-APR-1994

  • Trunc to the first of the month:

    TRUNC (TO_DATE ('12-MAR-1994'), 'MONTH') ==> 01-MAR-1994
TRUNC (TO_DATE ('17-MAR-1994'), 'MM') ==> 01-APR-1994

TO_Char 函式和Trunc 函式的一些組合：

  • Trunc back to the beginning of the current day (time is always midnight): 

    TO_CHAR (TRUNC (TO_DATE ('11-SEP-1994 10:00 AM',
                      'DD-MON-YYYY HH:MI AM'), 'DD'),
         'DD-MON-YYYY HH:MI AM')
==> 11-SEP-1994 12:00 AM

TO_CHAR (TRUNC (TO_DATE ('11-SEP-1994 4:00 PM',
                      'DD-MON-YYYY HH:MI AM'), 'DD'),
         'DD-MON-YYYY HH:MI AM')
==> 11-SEP-1994 12:00 AM

  • Trunc to the beginning of the current hour:

    TO_CHAR (TRUNC (TO_DATE ('11-SEP-1994 4:17 PM',
                      'DD-MON-YYYY HH:MI AM'), 'HH'),
       'DD-MON-YYYY HH:MI AM')
==> 11-SEP-1994 04:00 PM
語法：
dbtimezone
作用：
dbtimezone 返回資料庫時區值
Example:
SQL> SELECT DBTIMEZONE FROM DUAL;
DBTIME
------
+00:00
5: Extract(datetime)
語法：
EXTRACT (
YEAR
MONTH
DAY
[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/34596/viewspace-809730/，如需轉載，請註明出處，否則將追究法律責任。

相關文章