## SQL: Start day and End day of week

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_dfrom dualconnect 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_dfrom dualconnect 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 ``

``with t1 as (select to_char(sysdate + level, 'Day DD-MON-YYYY HH24:MI') as date_c,    (sysdate + level) as date_dfrom dualconnect 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 ``