Subtracting Dates

Restriction: This topic applies to Windows environments only.

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)