Creating a Date Expression

In this section:

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:

Formats for Date Values

Reference:

You can work with dates in one of two ways:

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;