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!

8 thoughts on “Date Maths – IBM DB2”

1. 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?

Like

1. agulland says:

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!

Like

2. Ravi says:

Great job Agulland. Very useful stuff. Keep up the good job.

Like

3. bodev2011 says:

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!!!

Like

4. Al Gulland says:

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

Like

5. bodev2011 says:

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.

Like

6. Siva Koteswara Rao says:

How can we find next Thursday’s date?

Like