本节介绍了基本算术操作符(+,-)的功能和可以用于处理日期/时间类型值的函数。

表 2显示了所有可以用于处理日期/时间类型值的函数,关于这些函数的更详细信息将在后面的部分中出现。在表 1中说明了基本算术操作符(+,-)的功能。对于格式化类型的函数,请参见用于各种数据类型的格式化函数。在进行本节的内容学习前,应该从章节日期/时间类型中熟悉了日期/时间类型的背景信息。

表 1. Date/Time Operators
Operator Example Result
+ DATE '2001-09-28' + 7 05-OCT-01 00:00:00
+ TIMESTAMP '2001-09-28 13:30:00' + 3 01-OCT-01 13:30:00
- DATE '2001-10-01' - 7 24-SEP-01 00:00:00
- TIMESTAMP '2001-09-28 13:30:00' - 3 25-SEP-01 13:30:00
- TIMESTAMP '2001-09-29 03:00:00' - TIMESTAMP '2001-09-27 12:00:00' @ 1 day 15 hours

表 2中显示的日期/时间函数中,DATE和TIMESTAMP数据类型可以互换使用。

表 2. Date/Time Functions
Function Return Type Description Example Result
ADD MONTHS(DATE, NUMBER) DATE Add months to a date. ADD MONTHS('28-FEB-97', 3.8) 31-MAY-97 00:00:00
CURRENT DATE DATE Current date. CURRENT DATE 04-JUL-07
CURRENT TIMESTAMP TIMESTAMP Returns the current date and time. CURRENT TIMESTAMP 04-JUL-07 15:33:23.484
EXTRACT(field FROM TIMESTAMP) DOUBLE PRECISION Get subfield. EXTRACT(hour FROM TIMESTAMP '2001-02-16 20:38:40') 20
LAST DAY(DATE) DATE Returns the last day of the month represented by the given date. If the given date contains a time portion, it is carried forward to the result unchanged. LAST DAY('14-APR-98') 30-APR-98 00:00:00
LOCALTIMESTAMP [ (precision) ] TIMESTAMP Current date and time (start of current transaction). LOCALTIMESTAMP 04-JUL-07 15:33:23.484
MONTHS BETWEEN(DATE, DATE) NUMBER Number of months between two dates. MONTHS BETWEEN('28-FEB- 07', '30-N0V-06') 3
NEXT DAY(DATE, dayofweek) DATE Date falling on dayofweek following specified date. NEXT DAY('16-APR- 07','FRI') 2 0-APR-07 00:00:00
NEW TIME(DATE, VARCHAR, VARCHAR) DATE Converts a date and time to an alternate time zone NEW TIME(T0 DATE '2005/05/29 01:45', 'AST', 'PST') 2005/05/29 21:45:00
ROUND(DATE [, format ]) DATE Date rounded according to format. R0UND(T0 DATE('29-MAY- 05'),'M0N') 01-JUN-05 00:00:00
SYS EXTRACT UTC(TIME STAMP WITH TIME ZONE) TIMESTAMP TIMESTAMP SYS EXTRACT UTC(CAST('24 -MAR-11 12:30:00PM - 04:00' AS TIMESTAMP WITH TIME ZONE)) 2 4-MAR-11 16:30:00
SYSDATE DATE Returns current date and time SYSDATE 01-AUG-12 11:12:34
SYSTIMESTAMP() TIMESTAMP Returns current date and time SYSTIMESTAMP 01-AUG-12 11:11:23.665 229 -07:00
TRUNC(DATE [format ]) DATE Truncate according to format. TRUNC(T0 DATE('2 9-MAY- 05'), 'MON') 01-MAY-05 00:00:00