Age Function

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)