IBM SC41-5210-04 manual Date arithmetic operation in Query for iSeries

Subtraction rules are different from addition rules because a date, time, or timestamp value cannot be subtracted from a duration. Also, subtracting two date, time, or timestamp values is not the same as subtracting a duration from a date, time, or timestamp value.

The following rules apply to date, time, and timestamp subtraction:

vIf the first operand is a date, the second operand must either be a:

–Date

–Date duration

–Character representation of a date

–Labeled duration of years, months, or days

vIf the second operand is a date, the first operand must either be a:

–Date

–Character representation of a date

vIf the first operand is a time, the second operand must either be a:

–Time

–Time duration

–Character representation of a time

–Labeled duration of hours, minutes, or seconds

vIf the second operand is a time, the first operand must either be a:

–Time

–Character representation of a time

vIf the first operand is a timestamp, the second operand must either be a:

–Timestamp

–Character representation of a timestamp

–Duration

vIf the second operand is a timestamp, the first operand must either be a:

–Timestamp

–Character representation of a timestamp

Date arithmetic operation in Query for iSeries

Dates can be subtracted, added to (incremented) or subtracted from (decremented).

Subtracting dates in Query for iSeries

The result of subtracting one date (DATE2) from another (DATE1) is a date duration that specifies the number of years, months, and days between the two dates. The data type of the result is a packed-decimal numeric. If DATE1 is greater than or equal to DATE2, DATE2 is subtracted from DATE1. However, if DATE1 is less than DATE2, DATE1 is subtracted from DATE2, and the sign of the result is made negative. The following procedural description clarifies the steps involved in the operation RESULT = DATE1 - DATE2.

If DAY(DATE2) < = DAY(DATE1)

then DAY(RESULT) = DAY(DATE1) - DAY(DATE2).

If DAY(DATE2) > DAY(DATE1)

then DAY(RESULT) = N + DAY(DATE1) - DAY(DATE2) where N = the last day of MONTH(DATE2).

MONTH(DATE2) is then incremented by 1.

If MONTH(DATE2) < = MONTH(DATE1)

then MONTH(RESULT) = MONTH(DATE1) - MONTH(DATE2).

If MONTH(DATE2) > MONTH(DATE1)

70Query for iSeries Use V5R2