Creating a Date Expression

A date expression performs a numeric calculation that involves dates.

A date expression returns a date, a date component, or an integer that represents the number of days, months, quarters, or years between two dates. You can write a date expression directly that consists of:

• A date constant. For example:

  COMPUTE END_DATE/MDYY = 'FEB 29 2000';

  This requires single quotation marks around the date constant.

• A date field. For example:

  COMPUTE NEWDATE/YMD = START_DATE;

• An alphanumeric, integer, or packed decimal format field, with date edit options. For example, in the second COMPUTE command, OLDDATE is a date expression:

  COMPUTE OLDDATE/I6YMD = 980307;
  COMPUTE NEWDATE/YMD DFC 19 YRT 10 = OLDDATE;

• A calculation that uses an arithmetic operator or date function to return a date. Use a numeric operator only with date formats (formerly called Smart dates). The following example first converts the integer date HIRE_DATE (format I6YMD) to the date format CONVERTED_HDT (format YMD). It then adds 30 days to CONVERTED_HDT:

  COMPUTE CONVERTED_HDT/YMD = HIRE_DATE;
  HIRE_DATE_PLUS_THIRTY/YMD = CONVERTED_HDT + 30;

• A calculation that uses a numeric operator or date function to return an integer that represents the number of days, months, quarters, or years between two dates. The following example uses the date function YMD to calculate the difference (number of days) between an employee hire date and the date of his first salary increase:

  COMPUTE DIFF/I4 = YMD (HIRE_DATE,FST.DAT_INC);

Formats for Date Values

You can work with dates in one of two ways:

• In date format. The value is treated as an integer that represents the number of days between the date value and a base date. There are two base dates for date formats:
  • YMD and YYMD formats have a base date of December 31, 1900.
  • YM and YYM formats have a base date of January, 1901 on z/OS and a base date of December 31, 1900 on Windows and UNIX.

  When displayed, the integer value is converted to the corresponding date in the format specified for the field. The format can be specified in either the Master File or in the command that uses an expression to assign a value to the field. These were previously referred to as smart date formatted fields.

• In integer, packed decimal, or alphanumeric format with date edit options. The value is treated as an integer, a packed decimal, or an alphanumeric string. When displayed, the value is formatted as a date. These were previously referred to as old date formatted fields.

You can convert a date in one format to a date in another format simply by assigning one to the other. For example, the following assignments take a date stored as an alphanumeric field, formatted with date edit options, and convert it to a date stored as a temporary date field:

COMPUTE ALPHADATE/A6MDY = '120599' ;
        REALDATE/MDY = ALPHADATE;

Base Dates for Date Formats

YMD, YYMD, MDYY, DMYY, MDY, and DMY

YM, YYM, MYY, and MY

YQ, YYQ, QYY, and QY

JUL and YYJUL

D
M
Y, YY
Q
W

Impact of Date Formats on Storage and Display

Performing Calculations on Dates

The format of a field determines how you can use it in a date expression. Calculations on dates in date format can incorporate numeric operators as well as numeric functions. Calculations on dates in integer, packed, decimal, or alphanumeric format require the use of date functions. Numeric operators return an error message or an incorrect result.

A full set of functions is supplied with your software, enabling you to manipulate dates in integer, packed decimal, and alphanumeric format. For details on date functions, see the ibi™ WebFOCUS® Using Functions manual.

COMPUTE TURNAROUND/I4 = SHIP_DATE - ORDER_DATE;

COMPUTE TURNAROUND/I4 = MDY(ORDER_DATE, SHIP_DATE);

Cross-Century Dates With DEFINE and COMPUTE

You can use an expression in a DEFINE or COMPUTE command, or in a DEFINE attribute in a Master File, that implements the sliding window technique for cross-century date processing. The parameters DEFCENT and YRTHRESH provide a means of interpreting the century if the first two digits of the year are not provided elsewhere. If the first two digits are provided, they are simply accepted.

Returned Field Format Selection

A date expression always returns a number. That number may represent a date, or the number of days, months, quarters, or years between two dates. When you use a date expression to assign a value to a field, the format selected for the field determines how the result is returned.

COMPUTE TURNAROUND/I4 = SHIP_DATE - ORDER_DATE;
COMPUTE DELIVERY/MDY = SHIP_DATE + 5;

Using a Date Constant in an Expression

When you use a date constant in a calculation with a field in date format, you must enclose it in single quotation marks; otherwise, it is interpreted as the number of days between the constant and the base date (December 31, 1900, or January 1, 1901). For example, if 022899 were not enclosed in quotation marks, the value would be interpreted as the 22,899th day after 12/31/1900, rather than as February 28, 1999.

COMPUTE START_DATE/MDY = '022899';

COMPUTE YEAR_TO_DATE/I4 = CURR_DATE - 'JAN 1 1999' ;

Extracting a Date Component

Date components include days, months, quarters, or years. You can write an expression that extracts a component from a field in date format. However, you cannot write an expression that extracts days, months, or quarters from a date that does not have these components. For example, you cannot extract a month from a date in YY format, which represents only the number of years.

COMPUTE SHIP_MONTH/M = SHIP_DATE;

COMPUTE ADD_THREE/M = SHIPMONTH + 3;

Combining Fields With Different Formats in an Expression

When using fields in date format, you can combine fields with a different order of components within the same expression. In addition, you can assign the result of a date expression to a field with a different order of components from the fields in the expression.

You cannot, however, write an expression that combines dates in date format with dates in integer, packed, decimal or character format.

COMPUTE DAYS_LATE/I4 = DATE_PAID - DUE_DATE;

COMPUTE RETURN_BY/DMY = DATE_SOLD + 7;