Subtracting Dates

In DB2 mode, when one date is subtracted from another, the result is a date duration that indicates the number of years, months and days. For example, the result of:

DATE('2/25/2000') - '12/20/1999'

is 205 (duration of 0 years, 2 months and 5 days). The data type of the result is DECIMAL(8,0).

The rules that apply to the date subtraction calculation are as follows:

• If DATE1 is greater than or equal to DATE2, then DATE2 is subtracted from DATE1.
• If DATE1 is less than DATE2, DATE1 is subtracted from DATE2 and the result is negative.

Using the operation RESULT = DATE1 - DATE2, these rules can be illustrated as follows:

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 is 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)
  Then  MONTH(RESULT) = 
        12 + MONTH(DATE1) - MONTH(DATE2)
  And  YEAR(DATE2) is incremented by 1
YEAR(RESULT) = YEAR(DATE1) - YEAR(DATE2)