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?

Answer 1

First Day of the Current Month:

CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS 

First of the Current Year is

CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS - (MONTH(CURRENT_DATE)-1) MONTHS 

Last Day of the Last Month:

CURRENT_DATE - DAY(CURRENT_DATE) DAYS 

First Day of the Last Month:

CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS - 1 MONTH 

Answer 2

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