Date and time durations

The BMC AMI Utilities SQL language supports the following types of durations:

Labeled durations

Durations labeled in accordance with the BMC AMI Utilities SQL language are large integers. In contrast, IBM Db2 internally treats labeled durations as DECIMAL(15,0) numbers.

In the BMC AMI Utilities SQL language, applying a labeled duration to a numerical expression whose type is not a large integer causes a syntax error. To correct the error, you can apply the INTEGER-or-INT function on the expression, before the labeled duration.

Example

Assume that column 'DEC15' is a DECIMAL(15,0). The following expression causes a syntax error because you can apply the labeled duration (DAYS) only to a large integer:

CURRENT DATE = DATE('1995-01-01') + DEC15 DAYS

Adding the INTEGER function corrects the error:

CURRENT DATE = DATE('1995-01-01') + INTEGER(DEC15) DAYS

Date durations

BMC AMI Utilities can treat any DECIMAL(8,0) or DECIMAL(9,0) field as a date duration. Date durations can be negative or positive, and you can use them to decrement or increment DATEs, respectively.

Example

If x is the date duration, DATE + x is equivalent to the following expression:

DATE+YEAR(x)YEARS+MONTH(x)MONTHS+DAY(x)DAYS

Important

Db2 supports only DEC(8,0). Also, negative dates are not well defined in Db2.

Time durations

BMC AMI Utilities can treat any DECIMAL(6,0) or DECIMAL(7,0) field as a TIME duration.

Important

Db2 supports only DEC(6,0).

Timestamp durations

BMC AMI Utilities can treat any DECIMAL(20,6) or DECIMAL(21,6) field as a TIMESTAMP duration.