8.9.5 GETAGE

(1) Specification format

scalar-function-GETAGE ::= GETAGE(birth-date,reference-date)
 
  birth-date ::= value-expression
  reference-date ::= value-expression

To Page Top

(2) Explanation of specification format

birth-date:

Specifies the person's birth date.

The following rules apply:

• Specify birth-date in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• The data type of birth-date must be DATE, TIMESTAMP, CHAR, or VARCHAR. In the case of CHAR or VARCHAR, you must specify a character string literal that adheres to the format of the predefined input representation of a date or time stamp. For details about predefined input representations, see 6.3.3 Predefined character-string representations.

• If a dynamic parameter is specified by itself for birth-date, the assumed data type of the dynamic parameter is DATE.

reference-date:

Specifies the reference date for calculating the person's age.

The following rules apply:

• Specify reference-date in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• The data type of reference-date must be DATE, TIMESTAMP, CHAR, or VARCHAR. In the case of CHAR or VARCHAR, you must specify a character string literal that adheres to the format of the predefined input representation of a date or time stamp. For details about predefined input representations, see 6.3.3 Predefined character-string representations.

• If a dynamic parameter is specified by itself for reference-date, the assumed data type of the dynamic parameter is DATE.

The following example illustrates the result of executing the scalar function GETAGE.

Example

Determine the age on September 30, 2014 of a person born on January 15, 1986.

GETAGE(DATE'1986-01-15',DATE'2014-09-30') → 28

To Page Top

(3) Rules

1. The data type of the execution result is INTEGER.

2. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

3. If birth-date or reference-date is the null value, the execution result will be the null value.

4. If reference-date is earlier than birth-date, the execution result will be 0.

5. The scalar function GETAGE returns a person's age on the reference date. The same day one year after the birth date counts as 1 year old. Note that a birth date of February 29 is treated as March 1 in non-leap years.

To Page Top

(4) Example

Example:

Using the data in the employees table (EMPLIST), determine the number of employees 30 years of age or older as of January 1, 2015. The column BIRTH holds the employees' birth dates.

SELECT COUNT(*) FROM "EMPLIST"
    WHERE GETAGE("BIRTH",DATE'2015-01-01')>=30

To Page Top