We help IT Professionals succeed at work.

ORACLE previous week date function

saved4use
saved4use asked
on
I'd like to create a report which will run every Monday morning showing data for the previous week; Sun - Sat.

CLOSED_DTTM > trunc(SYSDATE)- 7 is not working.

What should I use?

Thanks.
Comment
Watch Question

IT-specialist
BRONZE EXPERT
Commented:
WHERE CLOSED_DDTM >=  trunc(sysdate - 7,'WW')
AND    CLOSED_DDTM <  trunc(sysdate,'WW')

The WW truncation gives as start of the week: sunday 00.00 hrs

Author

Commented:
Will the above work even when, say,  the middle of the week is a new month?
BRONZE EXPERT

Commented:
yes author, the solution which flow01 will work fine even in case the middle of week is a new month...

what in actual it does it, it truncates the day to start of the week,..

for example 01/Mar/2012 is thursday,

select trunc(to_date('01-Mar-2012','DD-Mon-YYYY'),'WW') from dual

this will result in 26th Feb 2012, which is start of the week and by default the start of week in oracle is sunday..