Progress
SQL-92
Guide and Reference


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:

This is the syntax for a date_arith_expr:

SYNTAX
date_time_expr { + | - } int_expr 
  | date_time_expr - date_time_expr  

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. See the following examples: CAST Function (SQL-92 Compatible) and CONVERT Function (Progress Extension).

int_expr

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

EXAMPLES

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 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095