DB2 SQL DATE1 – DATE2 Best Examples for You

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 in DB2. (For the purposes of this operation, a month denotes the equivalent of a calendar page. 

How to 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 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.Search for DB2 JOBS HERE.

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.

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 in SQL Queries RESULT = DATE1 – DATE2.

DB2 Date complex examples:

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)

Author: Srini

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