How to group by week of the year dates or day dates that start on Sundays in oracle 10?

Question

Given a table:

day_date (form of yyyy-mm-dd) column2 column3

I want to group the data by weeks, and the start of each week goes from Sunday to Saturday, but I want to display the day_date.

I have looked around all over, and I'm rusty on my SQL.

Thanks!

Answer 1

Assuming that day_date is a datetime field and that you want to display the start of the week as the grouping field, try a query of the form:

select TRUNC(day_date)-TO_NUMBER(TO_CHAR(day_date,'D'))+1 week_start_date,
       MAX(column2), MAX(column3)
from yourtable
group by TRUNC(day_date)-TO_NUMBER(TO_CHAR(day_date,'D'))+1;

If day_date is a text field representing a date in the format yyyy-mm-dd, you will need to replace day_date in the query above with to_date(day_date,'yyyy-mm-dd') throughout.