AGE returns a decimal duration

When operating in NPS compatibility mode, the AGE scalar function returns a decimal duration instead of an integer value.

Whether NPS compatibility mode is being used depends on the setting of the SQL_COMPAT global variable:
  • When SQL_COMPAT='NPS', the AGE scalar function returns a decimal duration:
    • When a single argument is specified and the data type of the input argument is DATE, the AGE scalar function returns a date duration.
    • When a single argument is specified and the data type of the input argument is TIMESTAMP, the AGE scalar function returns a timestamp duration.
    • When two arguments are specified and the data type of both input arguments is DATE, the AGE scalar function returns a date duration.
    • When two arguments are specified and the data type of either input argument is TIMESTAMP, the AGE scalar function returns a timestamp duration.
    For more information about date and timestamp durations, see Datetime operations and durations.
  • Otherwise, the AGE scalar function returns an integer value as described in AGE scalar function.
If SQL_COMPAT='NPS'... ...is equivalent to...
AGE(date-expression) (CURRENT_DATE – date-expression)
AGE(timestamp-expression) (CURRENT_TIMESTAMP – timestamp-expression)
AGE(date-expression1,date-expression2) (date-expression1 - date-expression2)
AGE(timestamp-expression1,timestamp-expression2) (timestamp-expression1 - timestamp-expression2)