Use the Age Function to age values in a source column.
The source column can contain character, numeric, date, or timestamp
data. A CHAR or VARCHAR column has a maximum length of 256 bytes.
The Age Function is formatted as:
AGE(parameters)
- Define the Age Function to include one or more
aging parameters.
- Use commas or spaces to separate parameters in
the Age Function.
- Parameters can be specified in any order.
The following is a list of the parameters with
valid format and values:
Parameter |
Format |
Valid Values |
Column Name – Specify
the name of the source column if it differs from the destination column. |
SC=column-name
|
Column Name |
SRCCOL=column-name |
Default – Age dates based on the
date adjustment value specified in a process request. |
DEF |
Uses date adjustment value specified
in the process request. |
None – Do not age value. |
NONE |
Value should not be aged regardless of
specifications in the process request. |
Incremental – Incremental Aging
is based on a known time unit. Optim™ supports
date aging in single units (for example 20 years) or multiple units
(for example, 2 years, 3 months, 2 days). |
[ + or - ] nY |
nY
-2500 to +1581
nM -30000 to +30000
nW
-30000 to +30000 nD -99999 to +99999 |
[ + or - ] nM |
[ + or -] nW |
[ + or - ] nD |
(The plus [+] sign is
optional.) |
Specific Year – Age dates based
on a specific four‑digit year in the desired format. |
nnnnY |
1582 - 3999 |
Multiple/Rule – Age dates based
on the number of times to apply a business rule. If you define the
Age Function using the Multiple/Rule, you must also include the RULE
parameter. |
nnnnnR |
1 - 30000 |
Semantic Aging
Semantic
Aging is based on a set of rules that you define to manage dates that
occur on holidays, weekends, and so on. You can use Semantic Aging
to adjust dates so that they occur on valid business days.
- Calendar –
- Name of the calendar that defines the special dates
to which the rules apply. If you use CALENDAR, you must also specify
a RULE.
CA=calendar-name
CALENDAR=calendar-name
- Rule –
- Name of the rule that defines the adjustment for
special dates. If DEF is specified, the default rule specified in
the process request is used.
RU=rule-name
RULE=rule-name
RU=DEF
RULE=DEF
- Century Pivot –
- Determines the century for two-digit years. Enter
a value 00 to 99.
PI=nn
PIVOT=nn
- You define calendars and rules by selecting Calendar from
the Options menu in the main window. See Open the Calendar Editor for details.
- If you specify AGE(RU=DEF),
the RULE specified in a process request is used. You must specify
values for any other age function parameters.
- If you use RULE and do not specify a CALENDAR,
then the Age Function uses the default calendar you specify in a process
request.
- If you do not include CALENDAR, RULE, and PIVOT
where needed in the Age Function, the default values you specify in
a process request apply.
- To specify the correct century for a two-digit
year, you must include the PIVOT in the Age Function.
- If you specify a PIVOT value, all two-digit years
equal to or greater than the PIVOT value are placed in the 20th century
(19xx). All two‑digit years less than the PIVOT value are placed in
the 21st century (20xx). The default PIVOT is 65.
Date Formats
The
source date format and the destination date format must contain a
single valid date format and must be less than or equal to the length
of the destination column. The format string must be delimited by
single quotation marks.
- Source Date Format –
- Applies the source column format string to age
character and numeric columns.
SF='format-string'
SRCFMT='format-string'
If the source column is character
or numeric, you must use SRCFMT or a Source Exit Routine (SRCEXIT)
to describe the contents of the column. These parameters are mutually
exclusive. See Exit Routines for Column Maps for details.
- Destination Date Format –
- Applies the destination column format string to
age character and numeric columns.
DF='format-string'
DSTFMT='format-string'
If the destination column is character
or numeric, you can specify DSTFMT or a Destination Exit Routine (DSTEXIT).
If you do not specify a format for the destination, the date aging
function uses SRCFMT by default. The destination column for an AGE
function cannot be binary.
Use the following
character strings to specify components of the date format:
Year |
Month |
Day |
Time |
Parts/Second |
YYYY |
MONTH |
DDD |
HH |
FFFFFF |
CCYY |
MMM |
DD |
MI |
FFFFF |
YY |
MM |
D |
SS |
FFFF |
|
M |
|
|
FFF |
|
|
|
|
FF |
|
|
|
|
F |
- If you specify a question mark (?) in a
format string, the Age Function maps the character value as it is.
(Use the question mark to include slashes, dashes, periods, and so
on, in the date format.)
- If you specify an asterisk (*) in a format
string, the Age Function maps any remaining characters in the source
column to the destination column. (Use the asterisk when the column
value is a date concatenated to additional characters.)
Note: You can use the Calendar Utility to define a default separator
and a default output year. These defaults apply when the source and
destination formats require separators or a specific year.
Example 1
To age a date column
by 2 years, 6 months, 40 weeks, and 15 days, and then apply a rule,
format the Age Function as:
AGE(+2Y,+6M,+40W,+15D,RU=NEXTPAYDAY)
Example 2
To
age only the year portion in a date column to the year 2020, and apply
a rule, format the Age Function as:
AGE(2020Y,RU=NEXTWORKDAY)
Example 3
To
age a date column using MULTIPLE/RULE to increment by five occurrences
of a rule called NEXTSTRTQTR, using a calendar called PSAPRULE, format
the Age Function as:
AGE(CA=PSAPRULE,RU=NEXTSTRTQTR,5R)
Example 4
To
age data in a character or numeric column by the following parameters:
- A named source column.
- The source format, using the first two characters
for the last two digits of the year and the remaining 3 digits as
the day in the Julian calendar.
- A century pivot to determine the correct century
because the source is formatted with a two-digit year. The century
pivot in this example is 42. All two-digit years greater than or equal
to 42 are placed in the 20th century (19xx). All two-digit
years less than 42 are placed in the 21st century (20xx).
- Age date by 5 years.
Format the Age Function as:
AGE(5Y,SC=ORDER_DATE,SF='YYDDD',PI=42)