Oracle Date Function 讲解和事例

请大家记住

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

myhuaer 发表于:2005.11.10 13:39 ::分类: ( Oracle Infomation ) ::阅读:(562次)

ITPub博客

Oracle Date Function 讲解和事例【Blog 搬家】

Oracle Date Function 讲解和事例

最新文章