Writing Date Expressions

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:

- A date constant. For example:
COMPUTE StartDate/MDY= 'FEB 28 93';

Note the use of single quotation marks around the date constant FEB 28 93.

- A date variable. For example:
`COMPUTE NewDate = StartDate;`

- An alphanumeric, integer, or packed variable 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 addition, subtraction, or date functions to return a date. For example:
`COMPUTE Delivery/MDY = ShipDate + 5;`

- A calculation that uses subtraction or date functions to return an integer (not a date) that represents the number of days, months, quarters, or years between two dates. For example:
`COMPUTE ResponseTime/I4 = ShipDate - OrderDate;`

Formats for Date Values

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

- In date format, Maintain treats the value as a date. Date format interprets cross-century dates correctly, regardless of whether they are displayed with century digits. This is the preferred way of working with date values. The date is stored internally as an integer representing the number of days between the date and a standard base date. The base date is 12/31/1900 for all date variables declared in any operating environment using a 'D' for days, and also for all date variables declared in a Windows or UNIX environment using a 'Y' for years. The base date is 01/01/1901 for all date variables declared with a 'Y' in an S/390 environment.
- In integer, packed, or alphanumeric format with date edit options, Maintain treats the value as an integer, a packed decimal, or an alphanumeric string. When displaying the value, Maintain formats it to resemble a date.

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 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 |

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;

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.

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:

- Use a date constant in an expression.
- Extract a date component.
- Combine variables with different components in an expression.

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' ;

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 correctly returns the value 2, not 14:

COMPUTE ADDTHREE/M = SHIPMONTH + 3 ;

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.

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, where 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, such as the following expression:

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 ;

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). 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 get promoted.

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

Using Addition and Subtraction in a Date Expression

When performing addition or subtraction in a date expression:

- Adding a number to a date yields a date. It is up to you to make sure that the expression resolves to a meaningful value.
- Subtracting one date from another yields an integer that represents the difference between the two dates.

- When a date with format M or Q is subtracted from a higher type of date, the operand on the right is converted to the format of the operand on the left.
- When a two-component date is subtracted from a three-component date, or vice versa, the variable with the lesser format is promoted to the type of the variable with the higher format.
- When subtracting a Q format date from an M format date, or vice versa, the operand on the right is converted to the same format as the operand on the left.
- Subtracting a number from a date yields a date with the same format as the original date.
- You cannot subtract a date from a number, and you cannot add a date to a date.

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 |