Writing Date Expressions

In this section:

A date expression returns a date, a component of a date, or an integer that represents the number of days, months, quarters, or years between two dates.

A date expression can consist of the following components, highlighted below:


Top of page

x
Formats for Date Values

Maintain enables you to work with dates in one of two ways:

You can convert a date in one format to a date in another format simply be assigning one to the other. For example, the following assignment statements take a date stored as an alphanumeric variable formatted with date edit options and convert it to a date stored as a date variable:

COMPUTE AlphaDate/A6MDY = '120599';
        RealDate/MDY = AlphaDate;

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

 

Date Format

For example: MDY

Integer, Packed, or Alphanumeric Format

For example: A6MDY

Value

Stored

Displayed

Stored

Displayed

October 31, 1992

33542

10/31/92

103192

10/31/92

November 01, 1992

33543

11/01/92

110192

11/01/92



x
Evaluating Date Expressions

The format of a variable 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. If you need to perform calculations on dates in integer, packed, or alphanumeric format, we recommend that you first convert them to dates in date format, and then perform the calculations on the dates in date format.

Consider the following example, which calculates how many days it takes for your shipping department to fill an order by subtracting the date on which an item is ordered, OrderDate, from the date on which it is shipped, ShipDate:

COMPUTE TurnAround/I4 = ShipDate - OrderDate;

An item ordered on October 31, 1992 and shipped on November 1, 1992 should result in a difference of 1 day. The following table shows how the format affects the result:

 

Value in Date Format

Value in Integer Format

ShipDate = November 1, 1992

33543

110192

OrderDate = October 31, 1992

33542

103192

TurnAround

1

7000

If the date variables are in integer format, you can convert them to date format and then calculate TurnAround:

COMPUTE NewShipDate/MDY = ShipDate;
        NewOrderDate/MDY = OrderDate;
        TurnAround/I4 = NewShipDate - NewOrderDate;


x
Selecting the Format of the Result Variable

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 variable, the format you give to the variable determines how the result is displayed.

Consider the following commands. The first command calculates how many days it takes for your shipping department to fill an order by subtracting the date on which an item is ordered, ORDERDATE, from the date on which it is shipped, SHIPDATE. The second calculates a delivery date by adding 5 days to the date on which the order is shipped, SHIPDATE.

COMPUTE TURNAROUND/I4 = SHIPDATE - ORDERDATE ;
COMPUTE DELIVERY/MDY = SHIPDATE + 5 ;

In the first command, the date expression returns the number of days it takes to fill an order; therefore, the associated variable, 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 variable, DELIVERY, must have a date format.


Top of page

x
Manipulating Dates in Date Format

This section provides additional information on how to write expressions using values represented in date format. It describes how to:


Top of page

x
Using a Date Constant in an Expression

When you use a date constant in a calculation with variables in date format, you must enclose it in single quotation marks; otherwise, Maintain interprets it as the number of days between the constant and the base date (December 31, 1900). The following example shows how to initialize STARTDATE with the date constant 02/28/93:

COMPUTE STARTDATE/MDY = '022893' ;

Top of page

x
Extracting a Date Component

Date components include days, months, quarters, and years. You can write an expression that extracts a component from a variable in date format. The following example shows how you can extract a month from SHIPDATE, which has the format MDY:

COMPUTE SHIPMONTH/M = SHIPDATE ;

If SHIPDATE has the value November 23, 1992, the above expression returns the value 11 for SHIPMONTH. Note that calculations on date components automatically produce a valid value for the desired component. For example, if the current value of SHIPMONTH is 11, the following expression:

COMPUTE ADDTHREE/M = SHIPMONTH + 3 ;

correctly returns the value 2, not 14.

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


Top of page

x
Combining Variables With Different Components in an Expression

When using variables in date format, you can combine variables with a different order of components within the same expression. For example, consider the following two variables: DATE_PAID has the format YYMD and DUE_DATE has the format MDY. You can combine these two variables in an expression to calculate the number of days that a payment is late as follows:

COMPUTE DAYS_LATE/I4 = DATE_PAID - DUE_DATE ;

In addition, you can assign the result of a date expression to a variable with a different order of components from the variables in the expression. For example, consider the variable DATE_SOLD, which contains the date on which an item is sold, in YYMD format. You can write an expression that adds 7 days to DATE_SOLD to determine the last date on which the item can be returned, and then assign the result to a variable with DMY format, as in the following COMPUTE command:

COMPUTE RETURN_BY/DMY = DATE_SOLD + 7 ;

Top of page

x
Different Operand Date Formats

In an expression in a procedure, all date formats are valid. If you have an expression that operates on date variables with different formats (for example, QY and MDY), Maintain converts one variable to the format of the other variable in order to perform the operation.

However, there are a few types of date variables that you cannot use in a mixed-format date expression. These variables, formatted as single components such as a day of the week or year (formats D, W, Y, and YY), cannot be meaningfully converted to a more complete date (such as a year with a month). Of course, you can use these date variables in same-type date expressions.

If a date with format M is compared to a date with format Q (or vice versa), the operand on the right is converted to the format of the operand on the left, and then the comparison is performed.

For all other date-to-date comparisons, the date with the lesser format is promoted to the format of the higher date, where possible. If conversion is not possible, an error is generated.

The following conversion hierarchy applies to date formats:

Order

Date Format

1

Dates with three components (for example, MDY, YYMD, Julian dates).

2

Dates with two components, one of which is a month (for example, MYY or YM).

3

Dates with two components, one of which is a quarter (for example, YQ).

4

Single component M or Q.

5

All other formats.

Dates in the fifth category do not generally get promoted.

When you have dates of two different types, dates in the lower category are promoted to the higher type.



x
Using Addition and Subtraction in a Date Expression

When performing addition or subtraction in a date expression:



Example: Using Addition and Subtraction in a Date Expression

Given the following variable definitions

DECLARE Days/D = 23;
DECLARE OldYear/YY = 1960;
DECLARE NewYear/YY = 1994;
DECLARE YearsApart/YY;
DECLARE OldYearMonth/YM = 9012;
DECLARE NewYearMonth/YM;
DECLARE FullDate/YMD = 870615;

the following COMPUTE commands are valid:

COMPUTE
YearsApart = NewYear - OldYear;
NewYear = OldYear + 2;
NewYearMonth = OldYearMonth - FullDate;

However, the next series of COMPUTE commands are invalid, because they include date variables formatted as just a day (Days) or just a year (OldYear) in a mixed-format date expression:

COMPUTE
NewYear = FullDate - OldYear;
FullDate = OldYearMonth + Days;

WebFOCUS