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
Thanks Dimitri. That may be more readable than what I have used in the past. I always have to add comments around this so that I can understand it later:
ReplyDeleteSELECT to_char(to_date(:dt), 'Day, Mon dd, yyyy') dt,
MOD((8 - to_char(to_date(:dt), 'D')),7) + to_date(:dt) end_dt,
-1 * MOD((5 + to_char(to_date(:dt), 'D')),7) + to_date(:dt) beg_dt
FROM dual;
Hi Dimitri,
ReplyDeleteWouldn't it be easier to use the WW or IW parameter in the TO_CHAR function?
WW Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
IW Week of year (1-52 or 1-53) based on the ISO standard.
(see URL for more information)
Kees
Hi Kees,
ReplyDeleteAs always there are more ways to achieve the same.
In this case they wanted an Interactive Report broken down by week in the format Mon 6-SEP-2010 - Sun 12-SEP-2010. So I went with the solution I showed above. If you are more comfortable with WW, go with that. Whatever is easier for you really.
Dimitri
try this in your session:
ReplyDeletealter session set nls_language = 'Dutch'
oops...
this is an NLS independent way:
, trunc (date_d, 'iw') as start_dow
, trunc (date_d + 7, 'iw') - 1 as end_dow
Thanks Alex (and Kees),
ReplyDeleteSo I must agree it makes sense to use the IW method after all to be complete future proof incase of other nls sessions hit your database.
So the query becomes:
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
Hi Dimitri,
ReplyDeleteNice piece of work. I was wondering if I would be able to list all the weeks from today for the past one year. That is from today to 01/06/2014.
Hello, what do you mean with all the weeks?
ReplyDeleteCan you give an example of the output: e.g. 01-2014, 02-2014 ?