How to get week-wise data in Oracle

Question

I have made a matrix report in which need to display column dynamically based upon the selection parameter. I have a selection paramter of date.

If I select Date on selection paramater as "03/01/2010" i.e. 1st March 2010then it should display like 1st March - 7th March

Answer 1

It depends what you're after. If you're after the next 7 days then:

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

If you want say Monday to Sunday then 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 your passing in.

If you're not passing in a date but a string then the first one would become, using 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 could do something similar for the second. If you have to use to_date then date_col should have a function-based index on to_date(date_col,'dd/mm/yyyy') or if you're going to be converting it differently then that way.