dgielis_

Archive · 8 September 2010

SQL: Start day and End day of week

Written 16 years ago. Oracle APEX has moved on a lot since; check the current Oracle APEX posts too.

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 

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_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 

Originally published on Blogspot.

Comments

Comments appear after I've read them. Plain text; line breaks are kept. Privacy