Select first day of preceding month in (DB2) SQL

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

sql database date db2

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

sql database date db2

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)

CodeHunter