This topic describes the features of the basic arithmetic operators plus sign (+) and minus sign (-) and the functions that can be used to process date and time values.
Table 2 shows all the functions that can be used to process date and time values. For more information about these functions, see the subsequent topics. Table 1 illustrates the features of the basic arithmetic operators plus sign (+) and minus sign (-). For information about formatting functions, see Functions for formatting data types. Before you get started with this topic, make sure you are familiar with the background information about date and time types in Date and time type.
| 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 |
In the date and time functions listed in Table 2, the DATE and TIMESTAMP data types can be converted to each other.
| 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 |