Date arithmetic

Date values can be subtracted, incremented, or decremented.

Subtracting dates: 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 DECIMAL(8,0). If DATE1 is greater than or equal to DATE2, DATE2 is subtracted from DATE1. If DATE1 is less than DATE2, however, 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.

Date subtraction: 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) then MONTH(RESULT) = 12 + MONTH(DATE1) - MONTH(DATE2) and YEAR(DATE2) is incremented by 1.
  • YEAR(RESULT) = YEAR(DATE1) - YEAR(DATE2)

For example, the result of DATE('3/15/2005') - '12/31/2004' is 215 (or, a duration of 0 years, 2 months, and 15 days). In this example, notice that the second operand did not need to be converted to a date. According to one of the rules for subtraction, described under Datetime arithmetic in SQL, the second operand can be a string representation of a date if the first operand is a date.

Incrementing and decrementing dates: The result of adding a duration to a date, or of subtracting a duration from a date, is itself a date. (For the purposes of this operation, a month denotes the equivalent of a calendar page. Adding months to a date, then, is like turning the pages of a calendar, starting with the page on which the date appears.) The result must fall between the dates January 1, 0001 and December 31, 9999 inclusive. If a duration of years is added or subtracted, only the year portion of the date is affected. The month is unchanged, as is the day unless the result would be February 29 of a non-leap-year. Here the day portion of the result is set to 28, and the SQLWARN6 field of the SQLCA is set to W, indicating that an end-of-month adjustment was made to correct an invalid date. DB2 Application Programming and SQL Guide also describes how SQLWARN6 is set.

Similarly, if a duration of months is added or subtracted, only months and, if necessary, years are affected. The day portion of the date is unchanged unless the result would be invalid (September 31, for example). In this case the day is set to the last day of the month, and the SQLWARN6 field of the SQLCA is set to W to indicate the adjustment.

Adding or subtracting a duration of days will, of course, affect the day portion of the date, and potentially the month and year. Adding or subtracting a duration of days will not cause an end-of-the-month adjustment.

Date durations, whether positive or negative, can also be added to and subtracted from dates. As with labeled durations, the result is a valid date, and SQLWARN6 is set to W to indicate any necessary end-of-month adjustment.

When a positive date duration is added to a date, or a negative date duration is subtracted from a date, the date is incremented by the specified number of years, months, and days, in that order. Thus, DATE1+X, where X is a positive DECIMAL(8,0) number, is equivalent to the expression: 
  DATE1 + YEAR(X) YEARS + MONTH(X) MONTHS + DAY(X) DAYS
When a positive date duration is subtracted from a date, or a negative date duration is added to a date, the date is decremented by the specified number of days, months, and years, in that order. Thus, DATE1-X, where X is a positive DECIMAL(8,0) number, is equivalent to the expression: 
  DATE1 - DAY(X) DAYS - MONTH(X) MONTHS - YEAR(X) YEARS

Adding a month to a date gives the same day one month later unless that day does not exist in the later month. In that case, the day in the result is set to the last day of the later month. For example, January 28 plus one month gives February 28; one month added to January 29, 30, or 31 results in either February 28 or, for a leap year, February 29. If one or more months is added to a given date and then the same number of months is subtracted from the result, the final date is not necessarily the same as the original date.

If one or more months are added to a given date and then the same number of months is subtracted from the result, the final date is not necessarily the same as the original date. In addition, logically equivalent expressions might not produce the same result. For example, the following two expressions do not produce the same result: 
   (DATE('2005 01 31') + 1 MONTH) + 1 MONTH    -- results in 2005-03-28 
   DATE('2005 01 31') + 2 MONTHS               -- results in 2005-03-31
The order in which labeled date durations are added to and subtracted from dates can affect the results. When you add labeled date durations to a date, specify them in the order of YEARS + MONTHS + DAYS. When you subtract labeled date durations from a date, specify them in the order of DAYS - MONTHS - YEARS. For example, to add one year and one day to a date, specify: 
  DATE1 + 1 YEAR + 1 DAY
To subtract one year, one month, and one day from a date, specify: 
  DATE1 - 1 DAY - 1 MONTH - 1 YEAR