Date Maths – IBM DB2
This article continues the Date Maths series, this time with DB2 syntax.
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 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!