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.

Kent Olsen

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.

Kent

saved4use

ASKER

"Last week a month ago" = Monday to Saturday; hence the use of between 4/16/12 and 4/21/12 in my initial question.
Thanks.

momi_sabag

basically when you want to filter by date, you can use functions such as
- 10 days
- 2 months

etc...
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

ASKER CERTIFIED SOLUTION

sathyaram_s

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kent Olsen

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)
AS
(
  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

saved4use

ASKER

Exactly what I need. No need for manual intervention.
Thanks!