IBM ISERIES SC41-5210-04 - Manual (Page 82)

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:
v If 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
v If the second operand is a date, the first operand must either be a:
– Date
– Character representation of a date
v If 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
v If the second operand is a time, the first operand must either be a:
– Time
– Character representation of a time
v If the first operand is a timestamp, the second operand must either be a:
– Timestamp
– Character representation of a timestamp
– Duration
v If 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)
70 Query for iSeries Use V5R2