– Datetime subtraction operator

Subtracts a number of milliseconds from a DATETIME or a DATETIME-TZ to produce another DATETIME or DATETIME-TZ, or subtracts one DATETIME or DATETIME-TZ from another to produce an INT64 result in milliseconds.


`datetime` - { `milliseconds` \| `datetime` }


`datetime-tz` - { `milliseconds` \| `datetime-tz` }

An expression that evaluates to a DATETIME value.

An expression that evaluates to an integer value specifying a number of milliseconds.

An expression that evaluates to a DATETIME-TZ value.

This example returns the date and time exactly 24 hours ago (dtTime). It then recalculates the number of hours (iHours) from the number of milliseconds (iMsec) since then and displays the result:

DEFINE VARIABLE dtTime AS DATETIME NO-UNDO. DEFINE VARIABLE iMsec AS INT64 NO-UNDO. DEFINE VARIABLE iHour AS INTEGER NO-UNDO INITIAL 3600000. DEFINE VARIABLE fHours AS DECIMAL NO-UNDO. ASSIGN `dtTime = NOW - (24 * iHour)` `iMsec = NOW - DATETIME-TZ(dtTime)` fHours = iMsec / iHour. MESSAGE "A day earlier: " dtTime " " "Current hours since then: " fHours VIEW-AS ALERT-BOX.

DEFINE VARIABLE dtTime AS DATETIME NO-UNDO. 
DEFINE VARIABLE iMsec  AS INT64    NO-UNDO. 
DEFINE VARIABLE iHour  AS INTEGER  NO-UNDO INITIAL 3600000. 
DEFINE VARIABLE fHours AS DECIMAL  NO-UNDO. 
ASSIGN 
  dtTime = NOW - (24 * iHour) 
  iMsec  = NOW - DATETIME-TZ(dtTime) 
  fHours = iMsec / iHour. 
MESSAGE "A day earlier: " dtTime "  "  
        "Current hours since then: " fHours VIEW-AS ALERT-BOX.

To get the number of days between two DATETIME or DATETIME-TZ variable values, use the DATE function. For example:


num-days = DATE(dt2) - DATE(dt1)

This operation does not take the time portion into account.

To ensure the correct result when working with two DATETIME-TZ values, convert one of the values to the time zone of the other. For example:


ASSIGN temp-dttz = dt1 TIMEZONE(temp-dttz) = TIMEZONE(dt2) num-days = DATE(dt2) – DATE(temp-dttz).

To subtract a specific number of days and milliseconds from a DATETIME, use the DATETIME function. For example:


new-datetime = DATETIME( DATE(old-datetime) - days, MTIME (old-datetime) - milliseconds ).

The DATETIME function ensures the time portion remains within a valid range by borrowing a day from the date portion, when necessary.

To subtract a specific number of days and milliseconds from a DATETIME-TZ, use the DATETIME-TZ function. For example:


new-datetime-tz = DATETIME-TZ( DATE(old-datetime-tz) - days, MTIME (old-datetime-tz) – milliseconds, TIMEZONE(old-dateime-tz) ).

The DATETIME-TZ function ensures the time portion remains within a valid range by borrowing a day from the date portion, when necessary.