get previous week date in select query

Question

I have below query which i am using for reporting in Cognos. I am doing reporting every week on Monday for the previous week from Monday till Sunday.

Currently this date is hardcoded but i want to make it dynamic so that everytime when i run this report on Monday i dont have to change the date for previous week.

Is it possible i can make DAY_DATE dynamic using something like sysdate in select query ?

select ID,NAME,DAY_DATE      
from TEST_REPORT 
WHERE DAY_DATE BETWEEN to_date ('20170904', 'YYYYMMDD') and to_date ('20170910', 'YYYYMMDD');

Answer 1

You can calculate start and end dates of previous week from the current date using TRUNC (date) function.

Let say you are running the query on monday 2017-09-11, then on friday 2017-09-15, and the query must always generate a report for previous week.
This query calculates a start date of the current week:

SELECT trunc( date '2017-09-11', 'IW' ) as x,
       trunc( date '2017-09-15', 'IW' ) as y
from dual;

X                Y               
---------------- ----------------
2017/09/11 00:00 2017/09/11 00:00

---

To calculate a start date of the previous week, substract 1 day from the above dates, and use TRUCT again:

SELECT trunc( trunc( date '2017-09-11', 'IW' ) - 1, 'IW') as start_last_week,
       trunc( trunc( date '2017-09-15', 'IW' )  - 1, 'IW') as start_last_week1
from dual;

START_LAST_WEEK  START_LAST_WEEK1
---------------- ----------------
2017/09/04 00:00 2017/09/04 00:00

---

So, in your query just use this clause (date >= than a start of previous week and < that a start of current week):

WHERE DAY_DATE>= trunc( trunc( sysdate, 'IW' )  - 1, 'IW') 
  and DAY_DATE < trunc( sysdate, 'IW' )