Date arithmetic expressions compute the difference between date‑time expressions in terms of days or milliseconds. SQL supports these forms of date arithmetic:

• Addition and subtraction of integers to and from date‑time expressions
• Subtraction of one date-time expression from another

Syntax

 date_time_expr { + | - }int_expr
   |date_time_expr - date_time_expr

Parameters

date_time_expr

Returns a value of type DATE or TIME or TIMESTAMP. A single date‑time expression cannot mix data types, however. All elements of the expression must be the same data type.

Date‑time expressions can contain date-time literals, but they must be converted to DATE or TIME using the CAST, CONVERT, or TO_DATE functions.

int_expr

Returns an integer value. SQL interprets the integer differently depending on the data type of the date‑time expression:

• For DATE expressions, integers represent days
• For TIME expressions, integers represent milliseconds
• For TIMESTAMP expressions, integers represent milliseconds

Example

The following example manipulates DATE values using date arithmetic. SQL interprets integers as days and returns date differences in units of days:

SELECT C1, C2, C1-C2 FROM DTEST
c1                 c2           c1-c2
---------------------------------------
1956-05-07         1952-09-29  1316
   select sysdate, 
     sysdate - 3 ,
     sysdate - cast ('9/29/52' as date)
from dtest;
 sysdate        sysdate-3    sysdate-convert(date,9/29/52)
----------------------------------------------------------
1995-03-24     1995-03-21     15516

The following example manipulates TIME values using date arithmetic. SQL interprets integers as milliseconds and returns time differences in milliseconds:

select systime, 
     systime - 3000, 
     systime - cast ('15:28:01' as time) 
from dtest;
 systime      systime-3000   systime-convert(time,15:28:01)
----------------------------------------------------------
15:28:09     15:28:06       8000