AGE( ) function

Returns the age, in days, of a specified date compared to a specified cutoff date, or the current operating system date.

Syntax

AGE(date/datetime/string <,cutoff_date>)

Parameters

date/datetime/string

Datetime or Character. The field, expression, or literal value to age.

cutoff_date

Optional. Datetime or Character. Specifies the field, expression, or literal value to which dates in the date/datetime/string parameter are compared. If this parameter is omitted, the current operating system date is used as the cutoff date.

Output

Numeric.

Remarks

This function calculates the number of days between two dates. You can use it to compare two dates to determine overdue accounts or to perform aged analyses of balances.

If the value in the date/datetime/string parameter is more recent than the value in the cutoff_date parameter, or the operating system date if no cutoff_date is specified, a negative value is returned.

Both parameters can accept a datetime value, but the time portion of the value is ignored. You cannot use the AGE( ) function with time values alone.

Unlike the AGE command, which requires a literal date value for the cutoff date, the AGE( ) function allows you to also use a field for the cutoff date – for example, AGE(Payment_date, Due_date). Using the AGE( ) function in this manner is equivalent to calculating the difference between two date fields by subtracting them in an expression – for example, Due_date – Payment_date.

A datetime field specified for the date/datetime/string or cutoff_date parameters can use any date or datetime format, as long as the field definition correctly defines the format.

When specifying a literal date value for the date/datetime/string or cutoff_date parameters, you are restricted to the date formats in the table below, and you must enclose the value in backquotes, or single or double quotation marks – for example, `20141231` or "20141231".

Character fields containing date or datetime values must also match the formats in the table below. Character datetime values can use any combination of date, separator, and time formats. The date must precede the time, and there must be a separator between the two. Separators such as slashes (/) cannot appear between the individual components of dates, but colons (:) can appear between the individual components of times.

Date formats

Separator formats

Time formats

Examples

YYYYMMDD

single blank space

hhmmss

hh:mm:ss

`20141231`

"20141231"

YYMMDD

the letter ‘t’

hhmm

hh:mm

`20141231 235959`

"20141231 235959"

the letter ‘T’

hh

`141231 2359`

"141231 2359"

 

+/-hhmm

(UTC offset)

`141231t23`

"141231t23"

+/-hh

(UTC offset)

`141231t235959-0500`

"141231t235959-0500"

(Note: Do not use hh alone in the main time format with data that has a UTC offset. For example, avoid: hh+hhmm. Results can be unreliable.)

`141231T2359+01`

"141231T2359+01"

Examples

Example

Return value

AGE(Due_date)

The number of days between each date in the Due_date field and the current date.

  • Dates prior to the current date return a positive value equal to their age in days.

  • Dates after the current date return a negative value equal to the number of days in the future they occur.

AGE(Due_date, `20141231`)

The number of days between each date in the Due_date field and the cutoff date of December 31, 2014.

  • Dates prior to the cutoff date return a positive value equal to the number of days before the cutoff day they occur.

  • Dates after the cutoff date return a negative value equal to the number of days after the cutoff day they occur.

AGE(`20141231`, Due_date)

The number of days between December 31, 2014 and each date in the Due_date field.

  • Results are the same as the example immediately above, but the sign of the returned values (positive or negative) is reversed.

AGE(Payment_date, Due_date)

The number of days between each date in the Payment_date field and a corresponding date in the Due_date field.

  • Payment dates prior to due dates return a positive value, indicating timely payment.

  • Payment dates after due dates return a negative value, indicating late payment.

AGE(Payment_date, Due_date+15)

The number of days between each date in the Payment_date field and a corresponding date in the Due_date field plus a grace period of 15 days.

  • Payment dates prior to due dates, or up to 15 days after due dates, return a positive value.

  • Payment dates more than 15 days after due dates return a negative value, indicating late payment outside the grace period.

AGE(`20141231`)

The number of days between December 31, 2014 and the current date.

  • If a positive value is returned, it is equal to the number of days in the past December 31, 2014 occurred.

  • If a negative value is returned, it is equal to the number of days in the future December 31, 2014 occurs.

  • If ‘0’ is returned, December 31, 2014 is the current date.

AGE(`20130731`,`20141231`)

AGE("20130731","20141231")

AGE(`20130731`,"20141231")

AGE(`20130731 235959`,`20141231`)

518

The number of days between July 31, 2013 and December 31, 2014.

Extracting overdue payments

The following example extracts the name, amount, and invoice date for each record where the age of the invoice is greater than 180 days, based on a cutoff date of December 31, 2014.

EXTRACT FIELDS Name Amount Invoice_Date TO "Overdue"
IF AGE(Invoice_Date,`20141231`) > 180


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback