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

Output

Numeric.

Examples

Basic examples

No cutoff date

Returns the number of days between 31 Dec 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(`20141231`)

Returns the number of days between each date in the Due_date field and the current date:

AGE(Due_date)

Mixing data types

Returns 518, the number of days between the two specified dates:

AGE(`20130731`,`20141231`)

AGE("20130731","20141231")

AGE(`20130731`,"20141231")

AGE(`20130731 235959`,`20141231`)

Using cutoff dates and fields

Returns 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(Due_date, `20141231`)

Returns 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(`20141231`, Due_date)

Comparing dates in fields

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

Returns 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(Payment_date, Due_date+15)

Advanced examples

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

Remarks

How it works

The AGE( ) function calculates the number of days between two dates.

When to use AGE( )

Use AGE( ) to compare two dates to determine overdue accounts, to perform aged analyses of balances, or to perform any task that requires the number of elapsed days between two dates.

Negative return values

A negative value is returned if the date specified for date/datetime/string is more recent than the date specified as the cutoff_date, or the operating system date if no cutoff_date is specified.

Using a field for the cutoff date

Unlike the AGE command, which requires a literal date value for the cutoff date, the AGE( ) function allows you to 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

Parameter details

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

Specifying a literal date or datetime value

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

Do not use any separators such as slashes (/) or colons (:) between the individual components of dates or times. Colons are permitted in character time values.

• Datetime values – you can use any combination of the date, separator, and time formats listed in the table below. The date must precede the time, and you must use a separator between the two. Valid separators are a single blank space, the letter 't', or the letter 'T'.

  Example formats	Example literal values
  YYYYMMDD	`20141231` "20141231"
  YYMMDD	`141231` "141231"
  YYYYMMDD hhmmss	`20141231 235959` "20141231 235959"
  YYMMDDthhmm	`141231t2359` "141231t2359"
  YYYYMMDDThh	`20141231T23` "20141231T23"
  YYYYMMDD hhmmss+/-hhmm (UTC offset)	`20141231 235959-0500` "20141231 235959-0500"
  YYMMDD hhmm+/-hh (UTC offset)	`141231 2359+01` "141231 2359+01"
  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.