Search the Site

Sponsors

bottom corner

Get dates corresponding to calendar week days in Oracle

The code demonstrated in this article will allow you to get the dates reflecting calendar week days. This may be useful, for instance, if you are running a query to get records inserted between the previous calendar week.

This page is filed under keyword(s): 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');

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 20 Aug 2008

bottom corner