Date arithmetic expressions

SQL Reference

SQL Reference : OpenEdge SQL Language Elements : Data types : Relational operators : Date arithmetic expressions

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