Date Maths – IBM DB2

This article continues the Date Maths series, this time with DB2 syntax. DB2 Reference can be found at IBM and details of SQL functions are found in the section Database Reference > SQL > Functions

Please refer to the other articles in this series on SQL Server and Oracle.

IBM DB2 Date Functions

System Date

  CURRENT DATE

Current Week

  WEEK(CURRENT DATE)

Current ISO Week

  WEEK_ISO(CURRENT DATE)

Current Month as a number from 1 to 12

  MONTH(CURRENT DATE)

Current Quarter as a number from 1 to 4

  QUARTER(CURRENT DATE)

Current Year

  YEAR(CURRENT DATE)

Current Time

  CURRENT TIME

Current Date and Time

  CURRENT TIMESTAMP

To add or subtract a number of days, months or years

  CURRENT DATE +N {DAYS | MONTHS | YEARS}

where N is any positive or negative integer followed by one of the keywords ‘DAYS’, ‘MONTHS’ or ‘YEARS – weeks, quarters are not available. A similar expression is available for adding hours, minutes or seconds to a timestamp. The keywords can be combined as in the following example,

  SELECT CURRENT DATE +2 YEARS +3 MONTHS -5 DAYS FROM SYSIBM.SYSDUMMY1;

To calculate the number of days between two dates use,

  DAYS(LATER_DATE) - DAYS(EARLIER_DATE)

To convert a date to an integer

  DAYS

and the integer is the number of days since 1 Jan 0001.

To convert an integer to a date

  DATE

To return the name of the day e.g. “Friday” for a date

  DAYNAME

To get the day of the week as an integer where 1 represents Sunday.

  DAYOFWEEK

To get the day of the week as an integer where 1 represents Monday.

  DAYOFWEEK_ISO

The day number in a year with value in the range 1-366

  DAYOFYEAR

To get the name of a month

  MONTHNAME

DB2 Date Calculations

First day of the current week. If you have many cases where you need to calculate the first day of the week or last day of the week then you may wish to put this logic within a user defined function.

  DATE(1) + (((DAYS(CURRENT DATE) - DAYS(DATE(1)))/7)*7) DAYS

Above formula calculates number of days between the current date and the first date – DATE(1). The result is then divided by 7 and then multiplied by 7 and because of rounding this returns a whole number of weeks since the first date. DATE(1) returns Jan 1 0001 which was a Monday and so this formula will then use Monday as first day in week. If you wish to use Tuesday then replace both occurrences of DATE(1) with DATE(2) and if you wish start of week to be Sunday then replace with DATE(7)

First day of the current month

  DATE(1) + (YEAR(CURRENT DATE)-1) YEARS + (MONTH(CURRENT DATE)-1) MONTHS

First day of next month

  DATE(1) + (YEAR(CURRENT DATE)-1) YEARS + (MONTH(CURRENT DATE)) MONTHS

Last day of the current month

  DATE(1) + (YEAR(CURRENT DATE)-1) YEARS +
      (MONTH(CURRENT DATE)) MONTHS - 1 DAY

Last day of next month

  DATE(1) + (YEAR(CURRENT DATE)-1) YEARS +
      (MONTH(CURRENT DATE)+1) MONTHS - 1 DAY

First day of this year

  DATE(1) + (YEAR(CURRENT DATE)-1) YEARS

Last day of this year

  DATE(1) + (YEAR(CURRENT DATE)) YEARS - 1 DAY

Hope you’ll find these useful!

This article was published on March 26, 2009 by Al Gulland.

6 comments on “Date Maths – IBM DB2

  1. James

    Hi,

    with your calculation to obtain the first day of the current week, wouldn’t it be cleaner to write

    CURRENT DATE – (DAYOFWEEK_ISO(CURRENT DATE) – 1) DAYS

    ? (Swap DAYOFWEEK and DAYOFWEEK_ISO according to taste…) Or is your method computationally more efficient, or have I missed something?

  2. agulland Post author

    Hi, yes that certainly does look cleaner! I think I was just adapting the DB2 functions from what I had for SQL Server and so may have just simply missed this trick – doubt if my technique is any better. By extension you could adapt your formula to calculate last day of week and first and last days of next week. And I guess by using DAYOFMONTH and DAYOFYEAR functions you could do similar logic for first and last days for month and year. Thanks for comment, next time I have access to DB2 I should update article with these!

  3. bodev2011

    Awesome job done by Gulland. I wasn’t able to find db2 dates anywhere so straight forward.

    Can you also provide how to get:

    First day of PREVIOUS week
    Last Day of Previous week

    Also how can you get QUARTER in db2:

    first day of current/previous quarter
    Last day of current/previous/first quarter

    I couldnt find this info anywhere. Please assist.
    Thank you!!!

  4. Al Gulland Post author

    I’ve updated above article with link to IBMs DB2 online reference and also added function for obtaining quarter which is just QUARTER().

    I don’t have an answer for your other questions – I don’t have access to a DB2 system to try out some options – but I would think you could start with James’ suggestion for first day of current week and use something like,

    (CURRENT DATE – 7 DAYS) – (DAYOFWEEK_ISO(CURRENT DATE) – 1) DAYS

  5. bodev2011

    Thanks Gulland. I have used what you have mentioned as:
    First Day of Prev Week(Sat):
    (CURRENT DATE – 7 DAYS) -(DAYOFWEEK(CURRENT DATE))DAYS Last Day of Prev Week(Frid):
    CURRENT DATE – 7 DAYS) +(DAYOFWEEK(CURRENT DATE)) DAYS

    The above worked. Thanks again for your quick response. This was very helpful as always :) Also regarding the quarter info i was looking more for as how to get first day of Current/Prev Quarter and last day of Current/Prev quarter.

Leave a Reply