DATE function

Converts a single character string, a set of month, day, and year values, an integer expression, a DATETIME expression, or a DATETIME-TZ expression into a DATE value.

If the DATE function cannot produce a valid date given the specified argument(s), it returns a run-time error.


DATE ( `month` , `day` , `year` )

DATE ( `string` )

DATE ( `integer-expression` )

DATE ( `datetime-expression` )

A constant, field name, variable name, or expression whose value is an integer from 1 to 12, inclusive.

An expression whose value is an integer from 1 to the highest valid day of the month.

An expression whose value is the year (for example, 1994).

A character string containing a date value to convert into a DATE data type. The string value must have the format specified by the Date Format (-d) startup parameter (the default is mdy). Note that -d sets the display format, not the date storage format, which is fixed. Furthermore, date constants entered in procedures, or as initial values in the Data Dictionary, are always specified in month/day/year format.

You do not have to specify separator characters for the month, day, and year components of the date string; however, slashes(/), periods(.), and hyphens(-) are accepted as separator characters.

An expression that evaluates to a signed integer value that represents the number of days since the origin of the ABL date data type. Usually this integer is obtained from a previous operation where the date was converted to an integer using the INTEGER(ABL-date) or INT64(ABL-date) function.

The value of the expression cannot exceed the maximum date value, which is 12/31/32767.

Note: The resulting date from the DATE(integer-expression) function is guaranteed to be a valid ABL date only if the integer-expression originated from the INTEGER(ABL-date) or INT64(ABL-date) function.

An expression that evaluates to a DATETIME or DATETIME-TZ. The DATE function returns the date portion of the datetime-expression as a DATE.

If datetime-expression is a DATETIME-TZ, the DATE function returns the date relative to the time zone of the DATETIME-TZ value. For example, a DATETIME-TZ field, fdt, is created in London (time zone UTC+00:00) with a value of May 5, 2002 at 7:15:03.002 am. DATE (fdt) returns 05/05/2002, regardless of the session’s time zone.

This procedure reads data from an input file that contains date information from another system stored as character strings without slashes or dashes between month, day, and year. It tries to convert these dates to ABL dates. Some formats cannot be successfully converted. For example:

r-date.p
/* r-date.p / DEFINE VARIABLE cnum AS CHARACTER NO-UNDO FORMAT "x(3)". DEFINE VARIABLE cdate AS CHARACTER NO-UNDO FORMAT "x(16)". DEFINE VARIABLE iday AS INTEGER NO-UNDO. DEFINE VARIABLE imon AS INTEGER NO-UNDO. DEFINE VARIABLE iyr AS INTEGER NO-UNDO. DEFINE VARIABLE ddate AS DATE NO-UNDO. INPUT FROM VALUE(SEARCH("r-date.dat")). REPEAT: SET cnum cdate. ASSIGN imon = INTEGER(SUBSTR(cdate,1,2)) iday = INTEGER(SUBSTR(cdate,4,2)) iyr = INTEGER(SUBSTR(cdate,7,2)) / Works for years within 50 of 2000 / iyr = iyr + (IF (iyr < 50) THEN 2000 ELSE 1900) ddate = DATE(imon,iday,iyr).* DISPLAY ddate. END. INPUT CLOSE.

r-date.p

/* r-date.p */ 
DEFINE VARIABLE cnum  AS CHARACTER NO-UNDO FORMAT "x(3)". 
DEFINE VARIABLE cdate AS CHARACTER NO-UNDO FORMAT "x(16)". 
DEFINE VARIABLE iday  AS INTEGER   NO-UNDO. 
DEFINE VARIABLE imon  AS INTEGER   NO-UNDO. 
DEFINE VARIABLE iyr   AS INTEGER   NO-UNDO. 
DEFINE VARIABLE ddate AS DATE      NO-UNDO. 
INPUT FROM VALUE(SEARCH("r-date.dat")). 
REPEAT: 
  SET cnum cdate. 
  ASSIGN 
    imon  = INTEGER(SUBSTR(cdate,1,2)) 
    iday  = INTEGER(SUBSTR(cdate,4,2)) 
    iyr   = INTEGER(SUBSTR(cdate,7,2)) 
    /* Works for years within 50 of 2000 */ 
    iyr   = iyr + (IF (iyr < 50) THEN 2000 ELSE 1900) 
    ddate = DATE(imon,iday,iyr). 
  DISPLAY ddate. 
END. 
INPUT CLOSE.

r-date2.p
/* r-date2.p / DEFINE VARIABLE cnum AS CHARACTER NO-UNDO FORMAT "x(3)". DEFINE VARIABLE cdate AS CHARACTER NO-UNDO FORMAT "x(16)". DEFINE VARIABLE ddate AS DATE NO-UNDO FORMAT "99/99/9999". INPUT FROM VALUE(SEARCH("r-date.dat")). REPEAT: SET cnum cdate. ddate = DATE(cdate).* DISPLAY ddate. END. INPUT CLOSE.

r-date2.p

/* r-date2.p */ 
DEFINE VARIABLE cnum  AS CHARACTER NO-UNDO FORMAT "x(3)". 
DEFINE VARIABLE cdate AS CHARACTER NO-UNDO FORMAT "x(16)". 
DEFINE VARIABLE ddate AS DATE      NO-UNDO FORMAT "99/99/9999". 
INPUT FROM VALUE(SEARCH("r-date.dat")). 
REPEAT: 
  SET cnum cdate. 
  ddate = DATE(cdate). 
  DISPLAY ddate. 
END. 
INPUT CLOSE.

This example produces the following output. It produces no date for the first example since spaces are not a valid date separator:


cnum cdate ddate ---- ---------------- ---------- nyd 01 01 86 ad 11-28-52 11/28/1952 red 12/08/56 12/08/1956 nsm 10.01.01 10/01/2001 hrl 1/8/1994 10/08/1994