Creating a Date Expression

In this section:

Formats for Date Values
Performing Calculations on Dates
Cross-Century Dates With DEFINE and COMPUTE
Returned Field Format Selection
Using a Date Constant in an Expression
Extracting a Date Component
Combining Fields With Different Formats in an 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

Reference:

Base Dates for Date Formats
Impact of Date Formats on Storage and Display

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;

Reference: Base Dates for Date Formats

The following table shows the base date for each supported date format:

Format	Base Date
YMD, YYMD, MDYY, DMYY, MDY, and DMY	1900/12/31
YM, YYM, MYY, and MY	1901/01 on z/OS 1900/12/31 on Windows and UNIX
YQ, YYQ, QYY, and QY	1901 Q1
JUL and YYJUL	1900/365
D M Y, YY Q W	There is no base date for these formats; these are just numbers, not dates.

Note that the base date used for the functions DA and DT is December 31, 1899. For details on date functions, see the TIBCO WebFOCUS® Using Functions manual.

Reference: Impact of Date Formats on Storage and Display

The following table illustrates how the field format affects storage and display:

	Date Format (For example: MDYY)		Integer, Packed, Decimal, or Alphanumeric Format (For example: A8MDYY)
Value	Stored	Displayed	Stored	Displayed
February 28, 1999	35853	02/28/1999	02281999	02/28/1999
March 1, 1999	35854	03/01/1999	03011999	03/01/1999

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 TIBCO WebFOCUS® Using Functions manual.

Example: Calculating Dates

Assume that your company maintains a SHIPPING database. The following example calculates how many days it takes the shipping department to fill an order by subtracting the date on which an item is ordered, the ORDER_DATE, from the date on which it is shipped, the SHIPDATE:

COMPUTE TURNAROUND/I4 = SHIP_DATE - ORDER_DATE;

An item ordered on February 28, 1999, and shipped on March 1, 1999, results in a difference of one day. However, if the SHIP_DATE and ORDER_DATE fields have an integer format, the result of the calculation (730000) is incorrect, since you cannot use the numeric operator minus (-) with that format.

The following table shows how the field format affects the result:

	Value in Date Format	Value in Integer Format
SHIP_DATE = March 1, 1999	35854	03011999
ORDER_DATE = February 28, 1999	35853	02281999
TURNAROUND	1	730000

To obtain the correct result using fields in integer, packed, decimal, or alphanumeric format, use the date function MDY, which returns the difference between two dates in the form month-day-year. Using the function MDY, you can calculate TURNAROUND as follows:

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.

Example: Selecting the Format of a Returned Field

Consider the following commands, assuming that SHIP_DATE and ORDER_DATE are date-formatted fields. The first command calculates how many days it takes a shipping department to fill an order by subtracting the date on which an item is ordered, ORDER_DATE, from the date on which it is shipped, SHIP_DATE. The second command calculates a delivery date by adding five days to the date on which the order is shipped.

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

In the first command, the date expression returns the number of days it takes to fill an order; therefore, the associated field, TURNAROUND, must have an integer format. In the second command, the date expression returns the date on which the item will be delivered; therefore, the associated field, DELIVERY, must have a date format.

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.

Example: Initializing a Field With a Date Constant

The following command initializes START_DATE with the date constant 02/28/99:

COMPUTE START_DATE/MDY = '022899';

The following command calculates the number of days elapsed since January 1, 1999:

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.

Example: Extracting the Month Component From a Date

The following example extracts the month component from SHIP_DATE, which has the format MDYY:

COMPUTE SHIP_MONTH/M = SHIP_DATE;

If SHIP_DATE has the value March 1, 1999, the above expression returns the value 03 for SHIP_MONTH.

A calculation on a date component automatically produces a valid value for the desired component. For example, if the current value of SHIP_MONTH is 03, the following expression correctly returns the value 06:

COMPUTE ADD_THREE/M = SHIPMONTH + 3;

If the addition of months results in an answer greater than 12, the months are adjusted correctly (for example, 11 + 3 is 2, not 14).

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.

Example: Combining Fields With Format YYMD and MDY

Consider the two fields DATE_PAID and DUE_DATE. DATE_PAID has the format YYMD, and DUE_DATE has the format MDY. You can combine these two fields in an expression to calculate the number of days that a payment is late:

COMPUTE DAYS_LATE/I4 = DATE_PAID - DUE_DATE;

Example: Assigning a Different Order of Components to a Returned Field

Consider the field DATE_SOLD. This field contains the date on which an item is sold, in YYMD format. The following expression adds seven days to DATE_SOLD to determine the last date on which the item can be returned. It then assigns the result to a field with DMY format:

COMPUTE RETURN_BY/DMY = DATE_SOLD + 7;