Avatar of saved4use
saved4use
Flag 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.
DB2

Avatar of undefined
Last Comment
saved4use

8/22/2022 - Mon
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
sathyaram_s

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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!