UNITS

The UNITS operator converts an integer to an interval.

Syntax

expr UNITS qual[(scale)]

where qual can be one of:

  YEAR
  MONTH
  DAY
  HOUR
  MINUTE
  SECOND
  FRACTION(1-6)

expr is an integer expression.

Usage

The UNITS operator converts an integer expression to an INTERVAL value expressed in a single unit of time that you specify after the UNITS keyword.

If the left-hand operand evaluates to a decimal number, any fractional part is discarded before the UNITS operator is applied.

UNITS has a higher precedence than any arithmetic or boolean operator. As a result, a left-hand arithmetic expression that uses a UNITS operator must be enclosed in parentheses. For example, 10 + 20 UNITS MINUTES will be evaluated as 10 + (20 UNITS MINUTES) and give a conversion error. It must be written (10 + 20) UNITS MINUTES to get the expected result.

Arithmetic operations with UNITS can return an invalid date and raise the runtime error -1267. For example, DATETIME(2011-01-31) YEAR TO DAY + (1 UNITS MONTH) does not give a valid date.

Because the difference between two DATE values is an integer count of days rather than an INTERVAL data type, you might want to use the UNITS operator to convert such differences explicitly to INTERVAL values.

MAIN
  DEFINE d DATE
  LET d = TODAY + 200
  DISPLAY (d - TODAY) UNITS DAY
END MAIN