The ADD-INTERVAL function adds a time interval to, or subtracts a time interval from, a DATE, DATETIME, or DATETIME-TZ value using the following syntax:

Syntax

result = ADD-INTERVAL ( datetime , interval-amount , interval-unit ) .

To subtract dates and times, you use the ADD-INTERVAL function with an INTEGER value for the interval amount, and a unit of time (such as minutes, days, or weeks) for the interval unit. The result is a different DATE, DATETIME, or DATETIME-TZ value, depending on the data type of datetime.

Here is an example that adds two days to the order date to determine the estimated ship date:

DEF VAR est-ship-date as DATETIME-TZ.

est-ship-date = ADD-INTERVAL(order-date-tz, 2, "days").

Starting with Release 10.1B, you can subtract two datetimes, giving an INT64 with the number of milliseconds.

The INTERVAL function returns the time interval between two DATE, DATETIME, or DATETIME-TZ values using the following syntax:

Syntax

result = INTERVAL ( datetime1 , datetime2 , interval-unit ) .

The result is a the number of specified units (for example, minutes, days, or weeks) between the specified values date or datetime values.

The return data type of INTERVAL is INT64. Here is an example that uses the INTERVAL function to determine if an order shipped on time:

/* Find out if an order shipped within 5 days of the order date. 
** This example uses the sports2000 database */

FOR EACH Order WHERE Order.CustNum <= 3:
   DISPLAY Order.OrderNum Order.OrderDate Order.ShipDate 
      IF INTERVAL(Order.ShipDate, Order.OrderDate, "days") <= 5  
         THEN "yes" ELSE "no" LABEL "on-time?".
END.

In this example, the ship datetime should be five days after the order date.

The following example using ADD-INTERVAL and INTERVAL shows time-interval calculations with date and datetime values that produce results in milliseconds.

&SCOPED-DEFINE vab VIEW-AS ALERT-BOX
OUTPUT TO DATE.out.

DEF VAR d1 AS DATE.
DEF VAR dt1 AS DATETIME.
DEF VAR dt2 AS DATETIME.
DEF VAR dtz1 AS DATETIME-TZ.
DEF VAR dtz2 AS DATETIME-TZ.
DEF VAR i AS int64.
DEF VAR i2 AS INT.
DEF VAR m AS int64.
DEF VAR d AS int64.
DEF VAR y AS int64.
DEF VAR hours AS int64.
DEF VAR minutes AS int64.
DEF VAR seconds AS int64.
DEF VAR millis AS int64.
d1 = 02/13/1945.
dt1 = 1945-02-13T00:59:59.123.
dtz1 = 1945-07-02T23:01:02.123+2:00.

/* ADD-INTERVAL() */
DEF VAR mm AS int64.
mm = 200000000000000.
MESSAGE "pos - ADD-INTERVAL with milliseconds - " ADD-INTERVAL(dtz1, mm, 
   "milliseconds") {&vab}.
mm = -9999999999999.
MESSAGE "neg - ADD-INTERVAL with milliseconds - " ADD-INTERVAL(dtz1, mm,
   "milliseconds") {&vab}.
/* INTERVAL() */
dtz1 = 1945-07-30T23:01:02.123+2:00.
dtz2 = 1945-07-01T23:01:02.123+2:00.
MESSAGE "pos DTZ - INTERVAL with milliseconds - " INTERVAL(dtz1, dtz2,
   "milliseconds") {&vab}.
dtz2 = 1945-07-01T23:01:03.123+2:00.
MESSAGE "neg DTZ - INTERVAL with milliseconds - " INTERVAL(dtz1, dtz2,
   "milliseconds") {&vab}.
dt1 = 1945-07-13T00:59:59.123.
dt2 = 1945-02-13T00:59:59.123.
MESSAGE "pos DT - INTERVAL with milliseconds - " INTERVAL(dt1, dt2,
   "milliseconds") {&vab}.
dt2 = 1945-02-13T00:59:59.123.
MESSAGE "neg DT - INTERVAL with milliseconds - " INTERVAL(dt1, dt2,
   "milliseconds") {&vab}.
OUTPUT CLOSE.