I just got an interesting question: how do you know for a date, in which week (Monday - Sunday) it falls.
I created a little test script to get 31 days of data:
(date_c is the same as date_d, but in a more readable output. c stands for char, d for date)
select to_char(sysdate + level, 'Day DD-MON-YYYY HH24:MI') as date_c,
(sysdate + level) as date_d
from dual
connect by level <= 31 Next I used the NEXT_DAY function to get the first Monday after that date, and the same for the Sunday. As it will give you the results of the next week I had to subtract 7 days so I was sure it would fall under the week the date was in.
e.g. if you have a date Wednesday 8 of September, that person wanted to have Monday 6th-Sunday 12th. If I just did NEXT_DAY of 8-SEP I would get 13-SEP, that is why I first had to subtract 7 to go to a previous week and get the next Monday.
For the end date it's similar, but there you only have to subtract 1 day, as only the Sunday might be a problem, as the next Sunday is the next week.
So the final query became:
with t1 as (
select to_char(sysdate + level, 'Day DD-MON-YYYY HH24:MI') as date_c,
(sysdate + level) as date_d
from dual
connect by level <= 31 )
select date_c,
next_day(trunc(date_d)-7,'Monday') as start_day_of_week,
next_day(trunc(date_d)-1,'Sunday') as end_day_of_week
from t1 A more readable screenshot:
Update (after comments of Kees and Alex): the following solution looks even more elegant and is NLS independent:
with t1 as (
select to_char(sysdate + level, 'Day DD-MON-YYYY HH24:MI') as date_c,
(sysdate + level) as date_d
from dual
connect by level <= 31 )
select date_c,
trunc(date_d,'iw') as start_day_of_week,
trunc(date_d+7,'iw')-1 as end_day_of_week
from t1 
