CODEWITHSUNDEEP
sqloracle-database
user1099310
user1099310

Reputation: 133

How to get week-wise data in Oracle

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

Upvotes: 4

Views: 8960

Answers (2)

Adam Musch
Adam Musch

Reputation: 13638

To get week-wise data rather than next-seven-days data, use the TRUNCATE function with the W argument to partition 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 this truncates to is dependent on NLS data - in some localizations weeks start on Sunday; in others, Monday.

Upvotes: 4

Ben
Ben

Reputation: 52923

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.

Upvotes: 4

Related Questions