Get dates corresponding to calendar week days in Oracle

To do so, we will make use of Oracle’s next_day() function. The sample code is as follows:

select next_day(sysdate-14,'SUNDAY') as week_start, next_day(sysdate-7,'SATURDAY') as week_end from dual;

Assuming the current date is 20 Aug 2008, the above code will return 10 Aug 2008 and 16 Aug 2008, reflecting that the previous calendar week is between Sunday the 10th and Saturday the 16th. Thus, your query to get records inserted in the previous calendar week may look something like the following.

select record_id, record_stuff, entry_date
from bunch_of_records
where entry_date between 
	next_day(sysdate-14,'SUNDAY') and 
	next_day(sysdate-7,'SATURDAY');