DATE

Syntax

DATE(input_date, [input_date format])

Parameters

date_string

A string of text representing a valid date.

format

Optional, one of the keywords in the table under the "Valid Date Format Keywords," specifying the date format of date_string.

Description

The Interact DATE macro converts an input date into a format-neutral integer value.

The DATE macro is computed as follows: DATE (X) = 365 + the number of whole days elapsed from noon on January 1, 0001 A.D. To the DATE (X), an optional input DATE format keyword can be provided to specify how to parse the input DATE. If no format keyword is supplied, the default of DELIM_M_D_Y is used. For more information, see Valid Date Format keywords for additional information on valid date formats.

Date formats are either fixed-width (for example, the date February 28, 1970 is represented as 02281970 in MMDDYYYY format), or delimited (for example, February 28, 1970, 2-28-1970, or 02/28/1970 in the DELIM_M_D_YY format.

In delimited formats, delimiters are slash (/), dash(-), space ( ), comma (,), or colon (:); years can be represented by either 2 or 4 digits; and months can be fully spelled out (for example, February), abbreviated (for example, Feb), or numeric (for example, 2 or 02).

For all years specified as two-digits:

  • By default, Interact assumes that delimited two-digit dates are between the years of 1920-2020
  • Two-digit years less than the millennium cutoff (default is 20, but can be set by the JVM parameter) are considered to be in the 2000's.
  • Two-digit years greater than or equal to the threshold are considered to be in the 1900's.
Note:
  • Not all DATE Formats available in Campaign are supported by Interact.
  • For more information on two-digit years, see Valid Date Format keywords.
  • For more information on configuring the millennium cutoff for two-digit years, see the JVM Arguments section in the Interact Tuning Guide.

This macro is available in HCL® Interact.

Many business systems use Julian Date offsets. The result of Interact DATE() macro is related to the Julian Date as follows:

Julian Date = DATE(...) + 1,721,059 + fraction of day elapsed since previous noon.

Useful DATE() values for the A.D. period include:
  • January 1, 2050 A.D returns 748,749.
  • January 1, 2000 A.D returns 730,486
  • January 1, 1990 A.D returns 726,834
  • January 1, 1900 A.D returns 693,962
  • January 1, 0001 A.D returns 365
Note: In accordance with the ISO 8601 standard and XML schema definition for Date and the DateTime objects, the proleptic Gregorian calendar is used to compute the count of days elapsed. In this calendaring system, the hypothetical year 0000 A.D. is synonymous with 0001 B.C.

Examples

DATE("8/31/2000") returns the number 730,729.

DATE("8/31/2000",DELIM_MM_DD_YYYY) returns the number 730,729.

DATE("2015-01-01",DELIM_Y_M_D) returns the number 735,965.

DATE("01",DD), DATE("0101",MMDDD) and DATE("1970-01-01",DELIM_Y_M_D) return the number 719,529.

Related Functions

Function Description
DATE_FORMAT Converts dates from one format to another.
DATE_JULIAN Returns the Julian date of the input.
DATE_STRING Returns the date string of the Julian date.
CURRENT_DATE Returns the current date in a specified format.