DATE

The DATE data type stores calendar dates with a Year/Month/Day representation.

Syntax

DATE

Usage

Storage of DATE variables is based on a 4 byte integer representing the number of days since 1899-12-31 (Unix Epoch 1900-01-01 minus 1 day)

The value range is from 0001-01-01 (-693594) to 9999-12-31 (2958464) .

DATE variables are initialized to zero (=1899-12-31) in functions, modules and globals.

Several built-in functions and operators specific to the DATE type are available, such as MDY() and TODAY. For more details, see Date and time operators.

Data type conversions, input and display of DATE values are ruled by environment settings, such as the DBDATE and DBCENTURY enviroment variables. Dates can be formatted with the USING operator. For more details, see Formatting DATE values.

As date-to-string conversion is based on an environment settings, it is not recommended that you hard code strings representing dates:

LET date_var = "24/12/1998"      -- DBDATE dependant code
LET date_var = MDY(12,24,1998)   -- Portable code

To add or subtract a given number of days to a DATE, simply use a + or - arithmetic operator followed by an integer expression representing a number of days:

MAIN
  DEFINE d DATE
  LET d = TODAY
  LET d = d + 10   -- Add 10 days
  LET d = d - 20   -- Subtract 20 days
  DISPLAY "d = ", d USING "yyyy-mm-dd"
END MAIN

The difference of two dates returns the number of days:

MAIN
  DEFINE d1, d2 DATE
  LET d1 = MDY(12,24,1998)
  LET d2 = MDY(5,11,2010)
  DISPLAY "d2 - d1 = ", (d2-d1)
END MAIN

DATE values can be converted directly from/to DATETIME values:

MAIN
  DEFINE d DATE,
         dt DATETIME YEAR TO FRACTION(3)
  LET d = TODAY
  LET dt = d;   DISPLAY "dt = ", dt
  LET dt = CURRENT
  LET d = dt;   DISPLAY "d = ", d
END MAIN

In order to add or subtract a number of months to a DATE, use the UNITS operator:

MAIN
    DEFINE d0, d date
    LET d0 = MDY(01, 31, 2015)
    LET d = d0 + 1 UNITS MONTH; DISPLAY d
    LET d = d0 - 1 UNITS MONTH; DISPLAY d
    LET d = d0 - 2 UNITS MONTH; DISPLAY d
END MAIN

In fact, the UNITS operator will produce an INTERVAL. Then the DATE value is converted to a DATETIME, to add or subtract the INTERVAL value. Finally the DATETIME is converted to a DATE, in order to assign the result to the target variable.