AGE scalar function

The AGE function returns an integer value that represents the number of full years, full months, and full days between two timestamp values.

The schema is SYSIBM.

expression1 and expression2

An expression that returns a value of data type DATE, TIMESTAMP, CHAR, or VARCHAR. In a Unicode database, the expression can also be of data type GRAPHIC or VARGRAPHIC. A value of data type CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC must be a valid string that is accepted by the TIMESTAMP scalar function scalar function. Such a string is implicitly cast to a timestamp before conversion.

The default for expression2 is CURRENT TIMESTAMP(12).

If there is less than a full day between expression1 and expression2, the result is zero. If expression1 is earlier than expression2, the result is positive. If expression1 is later than expression2, the result is negative.

The following two expressions are equivalent:

AGE(expression1,expression2)
INTEGER((TIMESTAMP(expression2,12) – TIMESTAMP(expression1,12)) / 1000000)

For more information about the subtraction of timestamps and other timestamp arithmetic, see Datetime operations and durations.

Result

The result is the integer representation of the year, month, and day components of a timestamp duration. For example, the integer 71028 indicates that the timestamp returned by expression2 is 7 full years, 10 full months, and 28 full days later than the timestamp returned by expression1.

Notes

Determinism: AGE is a deterministic function.
If expression2 is not specified, the invocation of the AGE function depends on the value of the special register CURRENT TIMESTAMP, and consequently can be used only if special registers are supported (SQLSTATE 42621, 428EC, or 429BX).

Examples

Assume that CURRENT TIMESTAMP(12) is 2013-09-24-11.28.00.123456789012.

Set the host variable AGE1 to the number of full years, full months, and full days between 2012-02-28-12.00.00 and the current timestamp. (The time component of the specified timestamp is slightly later than that of the current timestamp.)
```
SET :AGE1 = AGE(TIMESTAMP '2012-02-28-12.00.00')
```
The host variable AGE1 is set to 10624. This represents the 1 full year, 6 full months, and 24 full days between the two timestamps.
Set the host variable AGE1 to the number of full years, full months, and full days between 2012-02-28-11.00.00 (one hour earlier than in the previous example) and the current timestamp. (The time component of the specified timestamp is slightly earlier than that of the current timestamp.)
```
SET :AGE1 = AGE(TIMESTAMP '2012-02-28-11.00.00')
```
The host variable AGE1 is set to 10625.
Set the host variable AGE1 to the number of full years, full months, and full days between 2013-09-23-12.00.00 and the current timestamp.
```
SET :AGE1 = AGE(TIMESTAMP '2013-09-23-12.00.00')
```
There is not one full day between the two timestamps, so the host variable AGE1 is set to 0.
Set the host variable AGE1 to the number of full years, full months, and full days between 2020-01-01 and 2013-09-24.
```
SET :AGE1 = AGE(DATE '2020-01-01', DATE '2013-09-24')
```
There are 6 full years, 3 full months, and 6 full days between the two timestamps, so the host variable AGE1 is set to -60306. The value is negative because the first timestamp is later than the second timestamp.