UNITS Operator

The UNITS operator specifies an INTERVAL value whose precision includes only one time unit. You can use UNITS in arithmetic expressions that increase or decrease one of the time units in an INTERVAL or DATETIME value.

If the num operand is not an integer, it is truncated to the largest whole number that is the same as (or nearer to zero than) the specified value when the database server evaluates the expression.

In the following example, the first SELECT statement uses the UNITS operator to select all the manufacturer.lead_time values, increased by five days. The second SELECT statement finds all the calls that were placed more than 30 days ago.

If the expression in the WHERE clause returns a value greater than 99 (maximum number of days), the query fails. The last statement increases the lead time for the ANZA manufacturer by two days:

SELECT lead_time + 5 UNITS DAY FROM manufact;

SELECT * FROM cust_calls WHERE (TODAY - call_dtime) > 30 UNITS DAY;

UPDATE manufact SET lead_time = 2 UNITS DAY + lead_time
   WHERE manu_code = 'ANZ';