CODEWITHSUNDEEP
crystal-reportsdb2
Steven
Steven

Reputation: 33

DB2: How do I pull the previous full months' data for a table.column?

I'm running into an issue with a DB2 query for a Crystal Report. What I need to do is to pull the previous full month's data for a column, even if the previous month is December of the previous year (as it would be in January, for example). For instance, I have this table named TABLE:

Name    Date
John    11/01/2019
Dave    12/15/2019
Frank   01/02/2020

I would need something like 

select DATE from TABLE where month(DATE) = month(x)

In this instance, x = the full previous month's data, which in this case, would be December 2019, so it should return"

Dave  12/15/2019"

What's the code to pull the previous full month's data, regardless of what the current month and year are? Of course it should be dynamic, so it will pull the previous full month's data regardless when the query is run.

Upvotes: 1

Views: 2538

Answers (3)

Mark Barinstein
Mark Barinstein

Reputation: 12314

You may use any date instead of current date to return the first and last days of month previous to this given date.
This works for any Db2 version.

SELECT 
  CURRENT DATE - DAY(CURRENT DATE) + 1 - 1 MONTH AS FIRST_DAY
, CURRENT DATE - DAY(CURRENT DATE)               AS LAST_DAY
FROM SYSIBM.SYSDUMMY1;

Upvotes: 0

Steven
Steven

Reputation: 33

I was actually able to pick apart the IBM documentation to use the DB2 version of DATEPART and DATEADD. Here's what I came up with:

WHERE (DATE_PART('MONTH', COLUMN_DATE) = DATE_PART('MONTH', CURRENT_DATE - 1 MONTH) AND
DATE_PART('YEAR', COLUMN_DATE) = DATE_PART('YEAR', CURRENT_DATE - 1 MONTH))

I hope this helps someone in the future.

Upvotes: 0

nfgl
nfgl

Reputation: 3202

Try this

select date from table
where date between last_day(current_date - 1 month) + 1 day - 1 month
              and last_day(current_date - 1 month)

or if your version of db2 knows first_day

select date from table
where date between first_day(current_date - 1 month)
              and last_day(current_date - 1 month)

Upvotes: 1

Related Questions