How to get weekly data in Oracle

Question

I made a matrix report in which I need to dynamically display a column based on a selection parameter. I have a date picker option.

If I select Date by the selection parameter as "03/01/2010", that is, March 1, 2010, it should be displayed as March 1 - March 7

Answer 1

It depends on what you need. If you are after the next 7 days, then:

select * from my_table where date_col between :my_date and :my_date + 7 

If you want to say Monday through Sunday, use the next_day function:

 select * from my_table where date_col between next_day(:my_date, 'Monday') - 7 and next_day(:my_date, 'Monday') 

Both where :my_date is the date of your transfer.

If you do not pass a date, but a string, then the first one will be used with the to_date function:

 select * from my_table where date_col between to_date(:my_date,'dd/mm/yyy') + 7 and to_date(:my_date,'dd/mm/yyy') 

and you can do something similar for the second. If you need to use to_date , then date_col must have a functional index on to_date(date_col,'dd/mm/yyyy') or if you are going to convert it differently then.

Answer 2

To get data by week, rather than data for the next seven days, use the TRUNCATE function with argument W to split the data into calendar weeks.

 select trunc(date '2012-01-30', 'w') from dual; TRUNC(DATE'2012-01- ------------------- 2012-01-29 00:00:00 

Note. The day of the week that truncates depends on the NLS data - in some weeks, localization starts on Sunday; in others, on Monday.