5 Power Thoughts on DB2 V11 Date Subtraction

Subtracting dates:

Idea-1

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.

Idea-2

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.

Idea-3

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)

Idea-4

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.(Ref:IBM v11 reference)

Idea-5

Subtracting from Dates:

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

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.