--- title: "DB2 SQL DATE Calculation Best Examples" description: "In DB2 calculating or finding duration between two dates is complex and very needed. The given examples are well explained and given clear information. You can use in your project readily." url: https://srinimf.com/2017/01/05/how-to-calculate-date-duration-in-db2-best-explained/ date: 2017-01-05 modified: 2026-10-10 author: "Srini" image: https://i0.wp.com/srinimf.com/wp-content/uploads/2017/01/desk-office-pen-ruler.jpg?fit=1880%2C1253&ssl=1 categories: ["DB2"] tags: ["Best DB2 DATE examples with solution"] type: post lang: en --- # DB2 SQL DATE Calculation Best Examples The adding or subtract a DATE is again a DATE in [DB2](http://www.indeed.com/jobs?indpubnum=6634974704162507&chnl=srinimf). Here’re the best examples of DATE calculation. Related[DB2 SQL DATE Calculation Best Examples](https://srinimf.com/2017/01/05/how-to-calculate-date-duration-in-db2-best-explained/) ## 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 - [5 Power Thoughts on DB2 V11 Date Subtraction](https://srinimf.com/2016/01/15/5-power-thoughts-on-db2-v11-date-subtraction/)DateJanuary 15, 2016 - [How to choose cleverly DATE,TIME OR TIMESTAMP for Table Columns](https://srinimf.com/2016/09/17/how-to-choose-cleverly-datetime-or-timestamp-for-table-columns/)DateSeptember 17, 2016 - [DB2- Date, Time, Timestamp](https://srinimf.com/2011/02/06/db2-date-time-timestamp/)DateFebruary 6, 2011 ### Share this: - - [Tweet](https://twitter.com/share) - [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2017%2F01%2F05%2Fhow-to-calculate-date-duration-in-db2-best-explained%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2017%2F01%2Fdesk-office-pen-ruler.jpg%3Ffit%3D1200%252C800%26ssl%3D1&description=DB2%20SQL%20DATE%20Calculation%20Best%20Examples) - - [Share on Tumblr](https://www.tumblr.com/share) - [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2017/01/05/how-to-calculate-date-duration-in-db2-best-explained/?share=jetpack-whatsapp) - [More](#) - - [Print (Opens in new window) Print](https://srinimf.com/2017/01/05/how-to-calculate-date-duration-in-db2-best-explained/?share=print) - [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2017/01/05/how-to-calculate-date-duration-in-db2-best-explained/?share=reddit) - [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2017/01/05/how-to-calculate-date-duration-in-db2-best-explained/?share=telegram) - [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%20SQL%20DATE%20Calculation%20Best%20Examples&body=https%3A%2F%2Fsrinimf.com%2F2017%2F01%2F05%2Fhow-to-calculate-date-duration-in-db2-best-explained%2F&share=email) - ### Like this: Like Loading…