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

date-of-birth:

Specifies the date of birth.

The following specification rules apply:

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

• The data type for date-of-birth should 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 alone as date-of-birth, the assumed data type of the dynamic parameter will be DATE type.

reference-date:

Specifies the reference date for calculating the full age.

The following specification rules apply:

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

• The data type for reference-date should 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 alone as reference-date, the assumed data type of the dynamic parameter will be DATE type.

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

Example:

Find the full age as of September 30, 2014 for a person whose date of birth is 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 the BIGINT type^#.

   #

   If the integer data type format is a legacy format, it will be an INTEGER type.

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

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

4. If the reference-date is before the date-of-birth, the execution result is 0.

5. The scalar function GETAGE returns the full age as of the reference date. A full year is counted as one year on the same day one year after the date of birth. If the date of birth is February 29, the date of birth is assumed to be 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