Select first day of preceding month in (DB2) SQL

Question

I need to check whether a given date is in the preceding month for a monthly query. I can get

CURRENT DATE - 1 MONTH

to compare the selected date with, but I can't assume the current date will be the first day of each month exactly. Is there a built in way to get the first day of the month without extracting the year and month and gluing it back together?

Solution 1

First day of this year:

date('0001-01-01') + year(current date) years - 1 year

---

First day of this month:

date('0001-01-01') + year(current date) years - 1 year + month(current date) months - 1 month

---

First day of last month:

date('0001-01-01') + year(current date) years - 1 year + month(current date) months - 2 months

---

If you don't need to maintain SQL compatibility with Db2 LUW v10.5 and older, you can also use these convenient Db2 LUW v11.1 scalar functions: THIS_WEEK() THIS_MONTH() THIS_QUARTER() and THIS_YEAR().

First day of this month:

THIS_MONTH(CURRENT DATE)

---

First day of last month:

THIS_MONTH(CURRENT DATE) - 1 MONTH

---

Last day of last month:

THIS_MONTH(CURRENT DATE) - 1 DAY

Solution 2

Here are snippets from my DB2 SQL Month Calculation Cheat Sheet:

Beginning of Current Month:

CURRENT DATE + 1 DAYS - DAY(CURRENT DATE) DAYS

End of Current Month:

LAST_DAY(CURRENT DATE)

Beginning of Prior Month:

CURRENT DATE + 1 DAYS - DAY(CURRENT DATE) DAYS - 1 MONTHS

End of Prior Month:

LAST_DAY(CURRENT DATE - 1 MONTHS)

Beginning of Next Month:

CURRENT DATE + 1 DAYS - DAY(CURRENT DATE) DAYS + 1 MONTHS

End of Next Month:

LAST_DAY(CURRENT DATE + 1 MONTHS)

Solution 3

In order to flesh this out with more info. The solution above is perfect if you want the First day of the month, say you want the Last day of the month, or in my case I want the last day of the next quarter.

From above I was doing

date('0001-01-31') + year(date(prevQdate))years - 1 year + month(prevQdate) months + 2 months)

Which wasn't giving me what I wanted, sometimes the date was the 30th instead of 31st for months with 31 days...

Changing it to

date('0001-01-31') + year(date(prevQdate))years - 1 year + **(month(prevQdate) + 2)** months)

Gave me what I wanted. Looks like the first addition of the Months from the prev quarter date was resetting the DAY part of my date and thus the second addition of months was working on a date with only 30 days in the month.

Just something to be wary of when using this method of date manipulation in DB2.

Solution 4

LUW 9.7 (and I think z/os) methods using built-in functions.

Last day of Current Month:

LAST_DAY(CURRENT DATE)

First day of Previous Month:

LAST_DAY(CURRENT DATE - 2 MONTHS) + 1 DAY or (LUW) TRUNCATE(CURRENT DATE - 1 month, 'MONTH')

First day of Current Month:

LAST_DAY(CURRENT DATE - 1 MONTH) + 1 DAY or (LUW) TRUNCATE(CURRENT DATE, 'MONTH');  

View more solutions

Comments

[Fred Sobotka]

I'm not seeing that function listed for DB2 for Linux, UNIX, and Windows platforms. It's probably available only for mainframe DB2.

[moleboy]

My bad. strictly z/OS. It never ceases to amaze me how a company can release a single product and not have functionality as universal as possible across platforms. I can't imagine there is a single reason this wouldn't work everywhere. Outside of that, I don't know another solution other than what you've posted below OR breaking the current date down into string chunks MONTH, DAY, YEAR, modifying the MONTH and DAY, and then rebuilding the new date.

[nearly_lunchtime]

Shame, this did look perfect when I looked it up.

[nearly_lunchtime]

Nice, these are fairly painless. Thanks.

[Fred Sobotka]

I hear ya. There are features in DB2 z/OS that us LUW folks are just barely getting in DB2 9.7, and other things that aren't anywhere close to being ported. That said, IBM is making a lot of progress reconciling SQL and features between mainframe DB2 and DB2 LUW (which is sometimes referred to derisively by the mainframe crows as "little" DB2). Conference sessions and user group presentations by well-known DB2 speakers such as Craig Mullins are pointing out the ever-growing set of SQL that works identically in both DB2 products. It's still not perfect, but I've seen significant progress.

[moleboy]

Hearing that makes me happy. Currently, I'm working with IBM's Maximo asset management system and IBM has been pretty lousy with it. I'm under the impression that it is supported primarily by the people who worked for the company IBM acquired it from. Its a product designed by people who don't see databases as anything more than (effectively) a series of flat files. And their support is miserable. If it ever gets folded into IBM's mainstream support, maybe I'll get lucky.

[Fred Sobotka]

The most reliable way to produce the last day of a month is to build an expression that produces the first day of the month after it, and then subtract one day. Last day of next quarter, for example is DATE('0001-01-01') + YEAR(CURRENT DATE) YEARS - 1 YEAR + ((QUARTER(CURRENT DATE) +1) * 3) MONTHS - 1 DAY