DB2 date function for previous week last month

saved4use used Ask the Experts™
Every Monday I run a query that pulls accts that were opened the previous week but a month ago. For example, yesterday (5/22) I used  "acct_open_dt between 4/16/12 and 4/21/12." I need to automate this date range.
What date function do I use?

Thanks for your tremendous assistance.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Saved,

You need to define what is "last week but a month ago".  Is it by calendar week starting on Sunday or Monday, etc.



"Last week a month ago" = Monday to Saturday; hence the use of between 4/16/12 and 4/21/12 in my initial question.
basically when you want to filter by date, you can use functions such as
- 10 days
- 2 months

the hard part in your question is to define exactly which date range you are after, once you have that, actually implementing it should be easy
if i understand you correctly, you want to get the week that ended exactly one month ago (if today is May 22nd, you want the week that ended in April 21st)
so you need something like

where acct_open_dt >= current date - 1 month - 7 days
and  acct_open_dt < current date - 1 month
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

i think your specification is quiet vague.
Making some assumptions, i hv done a piece of sql.

the code does the following
a)  get yesterdays(ie, sunday's) date and get the corresponding day of last month , for monday 05/21, sunday is 05/20 . from apr, it is 04/20

b) find the next saturday after the said date, ie for 04/20, it is 04/21

c) find the monday prior to 04/21 , ie 04/16

with tmp(dt) as
union all
select dt+1 day from tmp where dt<date('2012-12-31')
mondays(dt) as
select dt from tmp where dayofweek_iso(dt)=1
dtranges(dt,range_end,range_start) as
select dt, (dt - 1 day - 1 month) + (6 - dayofweek_iso(dt - 1 day - 1 month)) days ,  (dt - 1 day - 1 month) + (1-dayofweek_iso(dt - 1 day - 1 month)) days  from mondays
select dt,range_end,range_start
from dtranges

Open in new window

Here is the code that prints the desired date range for every monday of the year.
Hi Saved,

There are several ways to go about this.  A CTE allows you to compute the start date once and add 7 days for the end date.

WITHstart_dates (d)
  SELECT current_date - 1 month - ((dayofweek (current_date - 1month) - 1)  + 7) days
  FROM sysibm.sysdummy1
SELECT * FROM mytable
WHERE tabledate >= (SELECT d FROM start_dates)
  AND tabledate < (SELECT d + 7 days FROM start_dates);

Open in new window


Exactly what I need. No need for manual intervention.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial