DB2 SQL DATE Calculation Best Examples

The adding or subtract a DATE is again a DATE in DB2. Here’re the best examples of DATE calculation.

DB2: Add Months to DATE

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 the 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 days will, of course, affect the day portion of the date, and potentially the month and year. Adding or subtracting days will not cause an end-of-the-month adjustment.

DB2: DATE Duration

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.

Subtracting DATE

  • 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.

DATE Duration = DATE1 – DATE2.

DB2 SQL DATE Examples

DAY

  • 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.

Month

  • 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

YEAR(RESULT) = YEAR(DATE1) – YEAR(DATE2)

Related Posts

Author: Srini

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