Search code examples
sqldatabasedatedb2

Select first day of preceding month in (DB2) SQL

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

  • Available in all Db2 versions

    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

    Available in Db2 11.1 and newer

    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

    A note about ROUND_TIMESTAMP()

    ROUND_TIMESTAMP() is available in Db2 9.7 and newer, but given that it by design rounds some input values down and others up, ROUND_TIMESTAMP() is not an ideal way to reliably return the first day of the current month or previous month.

    As pointed out in the original question, CURRENT DATE - 1 MONTH will only return the first day of the previous month (the desired result) when run on the first day of the month, but ROUND_TIMESTAMP(CURRENT DATE - 1 MONTH, 'W') only extends that behavior for three more days, until the fifth, when it will start returning the eighth day of the previous month. Similarly, ROUND_TIMESTAMP(CURRENT DATE - 1 MONTH, 'MM') will only return the first day of the previous month up to and including the 15th, after which it will round upward and return the first day of the current month.