How to get data for a particular date range as a parameter

[anuragrathor]

I have a "Date Range" Section, with these three items:-
Last full week; Last full month; Last full quarter. I want to get the data depending upon the selection of the Date Range.

• If today is March 8th and I click on "last full week", the date range would actually 03/01/10 - 03/06/10. The last full week starting on Sunday and ending on Saturday.
• If today is March 8th and I click on "last full month", the date range would be 02/01/10 - 03/01/10. It would go back the last full month available. March is not included because we haven't had a full month.
• If today is March 8th and I click on "last full quarter", then the date range would be 12/01/2009 - 02/01/2010. The last full quarter (months).

This will obviously change as today's date changes. I am not sure how is this thing to be done. Please help.

[rski]

If today is March 8th and I click on "last full week", the date range would actually 03/01/10 - 03/06/10. The last full week starting on Sunday and ending on Saturday.

The date you give is from monday till saturday (not from from sunday). Shouldn't there be 02/28/10-03/06/10?

[anuragrathor]

Oh..Sorry my bad :-)
The date range should be 02/28/10 - 03/06/10 . i.e. from Sunday to Saturday.

[rski]

try this

last week

select to_char(next_day(sysdate+5,'MON') - interval '14' day) ||'<->'|| to_char(next_day(sysdate+5,'MON') - interval '8' day) as "LAST FULL WEEK" from dual;
 

last month

select to_char(last_day(add_months(last_day(sysdate),-2))+1)||'<->'|| to_char(last_day(add_months(last_day(sysdate),-1))) as "LAST FULL MONTH" from dual;
 

last quarter

select to_char(last_day(add_months(last_day(sysdate),-4))+1)||'<->'|| to_char(last_day(add_months(last_day(sysdate),-1))) as "LAST FULL QUARTER" from dual;