This section describes the basic arithmetic operators (+, -) and functions for processing date and time values.
Table 1 explains the function of the basic arithmetic operators (+, -). Table 2 lists all the functions that you can use to process date and time values. These functions are described in more detail in a later section. For information about formatting functions, see Formatting functions (numeric, character, and date types). Before you start this section, familiarize yourself with the background information in Date/time types.
Table 1. Date and time operators
Operator | Example | Return value |
+ | 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 |
In the date and time functions shown in Table 2, the DATE and TIMESTAMP data types can be used interchangeably.
Table 2. Date and time functions
Function | Return data type | Description | Example | Return value |
ADD MONTHS(DATE, NUMBER) | DATE | Adds a number of months to a specified date. | ADD MONTHS('28-FEB-97', 3.8) | 31-MAY-97 00:00:00 |
CURRENT DATE | DATE | Returns the 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 | Extracts a subfield. | EXTRACT(hour FROM TIMESTAMP '2001-02-16 20:38:40') | 20 |
LAST DAY(DATE) | DATE | Returns the last day of the month for a specified date. If the specified date includes a time, the result also includes that time. | LAST DAY('14-APR-98') | 30-APR-98 00:00:00 |
LOCALTIMESTAMP [ (precision) ] | TIMESTAMP | Returns the current session timestamp. | LOCALTIMESTAMP | 04-JUL-07 15:33:23.484 |
MONTHS BETWEEN(DATE, DATE) | NUMBER | Returns the number of months between two specified dates. | MONTHS BETWEEN('28-FEB- 07', '30-N0V-06') | 3 |
NEXT DAY(DATE, dayofweek) | DATE | Returns the date of the first specified day of the week that is after the specified date. | NEXT DAY('16-APR- 07','FRI') | 2 0-APR-07 00:00:00 |
NEW TIME(DATE, VARCHAR, VARCHAR) | DATE | Converts a specified date and time to another time zone. | NEW TIME(T0 DATE '2005/05/29 01:45', 'AST', 'PST') | 2005/05/29 21:45:00 |
ROUND(DATE [, format]) | DATE | Formats the specified date. | R0UND(T0 DATE('29-MAY- 05'),'M0N') | 01-JUN-05 00:00:00 |
SYS EXTRACT UTC(TIME STAMP WITH TIME ZONE) | TIMESTAMP | Converts a timestamp with a time zone to a UTC 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 the current system date and time. | SYSDATE | 01-AUG-12 11:12:34 |
SYSTIMESTAMP() | TIMESTAMP | Returns the current system timestamp. | SYSTIMESTAMP | 01-AUG-12 11:11:23.665 229 -07:00 |
TRUNC(DATE [format]) | DATE | Truncates a specified date based on the specified format. | TRUNC(T0 DATE('2 9-MAY- 05'), 'MON') | 01-MAY-05 00:00:00 |