DB2 query to fetch last month of data. on current month-CodePudding

Every month I have to fetch records for the previous month from a Db2 database. How can I write a Db2 query to fetch the last month of data without hard-coding the date range? For example, when run in December 2021, the query would return records dated between '2021-11-01' AND '2021-11-30', and those dates would change dynamically when I run the same query a month later.

CodePudding user response：

It's easy to can precompute the date range in a cte and then use it in the main query. Assuming your table t has a ts column to filter by, you can do:

with
r as (
  select
    to_date(year(c) || '-' || month(c) || '-01' , 'YYYY-MM-DD') as e,
    to_date(year(p) || '-' || month(p) || '-01' , 'YYYY-MM-DD') as b
  from (
    select current date as c, current date - 1 month as p from sysibm.sysdummy1
  ) x
)
select * 
from t 
cross join r
where t.ts >= r.b and t.ts < r.e

See example at db<>fiddle.

CodePudding user response：

There are a few ways to describe the prior month as a date range in a Db2 SQL query. Some datetime SQL functions are not available on Db2 for z/OS, but even then you can still use date arithmetic and the LAST_DAY() function.

First day of last month: LAST_DAY(CURRENT DATE - 2 MONTHS) 1 DAY

Last day of last month: LAST_DAY(CURRENT DATE - 1 MONTH)

First day of this month: LAST_DAY(CURRENT DATE - 1 MONTH) 1 DAY

Inclusive-exclusive example (preferred approach):

SELECT ... WHERE someDateTimeColumn >= LAST_DAY(CURRENT DATE - 2 MONTHS)   1 DAY 
AND someDateTimeColumn < LAST_DAY(CURRENT DATE - 1 MONTH)   1 DAY

Inclusive-inclusive example (might miss some qualifying rows due to implicit type conversion):

SELECT ... WHERE someDateTimeColumn >= LAST_DAY(CURRENT DATE - 2 MONTHS)   1 DAY 
AND someDateTimeColumn <= LAST_DAY(CURRENT DATE - 2 MONTHS)

If you're querying Db2 for LUW v11.1 or newer, you can also call the THIS_MONTH() function to get the first day of an input month.

First day of last month: THIS_MONTH(CURRENT DATE - 1 MONTH)

First day of this month: THIS_MONTH(CURRENT DATE)

Inclusive-exclusive example:

SELECT ... WHERE someDateTimeColumn >= THIS_MONTH(CURRENT DATE - 1 MONTH) 
AND someDateTimeColumn < THIS_MONTH(CURRENT DATE)