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!
Al Gulland 
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?
LikeLike
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!
LikeLike
Great job Agulland. Very useful stuff. Keep up the good job.
LikeLike
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!!!
LikeLike
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
LikeLike
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.
LikeLike
How can we find next Thursday’s date?
LikeLike