DATE BTW
The DATE BTW statement calculates the number of date/time units (years, months, days), between two date/time fields. It can be used to generate aging categories for reports, by comparing a value for date due with the current date to determine which customers have balances of less than 30 days, 30 to 60 days, over 60 days, and so forth.
DATE BTW
(1)
(2) (3) (4)(5) (6)
(7) (8)
|
(8) To occurrence (constant/index) | |
|
(4) From occurrence (constant/index) |
|
|
(5) Unit (8:centuries,
7:years, 6:months, 5:days, 4:hours, 3:minutes, |
The unit (parameter 5) indicates the units of the result of the calculation. The result is returned in the predefined field BETWEEN.
If the from date is greater than the to date, BETWEEN is negative. If the from date is less than the to date, BETWEEN is positive.
When you use months in DATE BTW, the result is zero if the from month and year are the same as the to month and year. In other words, if from is February 1, 2000, and to is February 28, 2000, BETWEEN is zero. If the from month is different from the to month, BETWEEN is zero unless there is at least a 28-day difference in the two dates. In other words, January 31 to February 5 is zero months, but January 31 to February 28 -- or January 1 to February 1 -- is one month.
Both fields in the statement must be date/time field types. Also, results can be unpredictable if you are using a date field without a century component. Confirm that your System Administrator has set up the Pivot Year environment variable to avoid unexpected results.
If either date is null, a runtime error occurs.
The following example uses the DATE BTW statement to compare the date due with the current date to determine categories of aging for an accounts receivable report:
SET
DATE TAR WORK CURRENT DATE
DATE BTW TAR
INVOICE DUE DATE 5 TAR
WORK CURRENT DATE
IF ---
BETWEEN LT 30
T SET TAR
WORK CURRENT AMOUNT = TAR
INVOICE AMOUNT
F IF ---
BETWEEN LT 60
FT SET TAR
WORK OVER 30 DAYS = TAR
INVOICE AMOUNT
FF SET TAR
WORK OVER 60 DAYS = TAR
INVOICE AMOUNT