Link to home
Start Free TrialLog in
Avatar of saved4use
saved4useFlag for United States of America

asked on

DB2 date function for previous week last month

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.
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

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.

Avatar of saved4use


"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
Avatar of sathyaram_s
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.