AGE( ) function

Returns the number of elapsed days (the age) between a specified date and a specified cutoff date, or the current operating system date, or the number of elapsed days between any two dates.

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.

Returns -518:

AGE(`20141231`, `20130731`)

If you want the elapsed number of days between two dates to always be a positive number, regardless of which date is more recent, nest the AGE( ) function inside the ABS( ) function.

Returns 518:

ABS(AGE(`20141231`, `20130731`))

Using AGE( ) with datetime data

The AGE( ) function can accept datetime data in one or both parameters. However, you need to be careful if the time portion of the data includes a UTC offset (timezone indicator).

Datetime data without a UTC offset

The time portion of a datetime value does not affect the date calculation performed by AGE( ) if the time data does not include a UTC offset.

Datetime data with a UTC offset

The time portion of a datetime value can affect the date calculation performed by AGE( ) if the time data in one or both parameters includes a UTC offset. Analytics automatically reconciles the UTC offset before performing the calculation, which can cause the result to change by 1 day if reconciliation moves the time forward or backward through the boundary of midnight.

For more information, see How UTC offsets affect datetime expressions.

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.