Just a quick question.
Wouldn't this be easier?

Select next_day(Trunc(Sysdate) - 5, 'Monday') + (Level - 1)
From   dual
Connect By Level <= 5

(Out of convenience I used sysdate, instead of a variable)

SQL> variable x varchar2(20)
SQL> exec :x := '26-SEP-2016'

PL/SQL procedure successfully completed.

SQL>
SQL> Select next_day(to_date(:x,'DD-MON-YYYY'), 'Monday') + (Level - 1)
  2  From   dual
  3  Connect By Level <= 5;

NEXT_DAY(
---------
03-OCT-16
04-OCT-16
05-OCT-16
06-OCT-16
07-OCT-16

SQL>
SQL> variable x varchar2(20)
SQL> exec :x := '01-OCT-2016'

PL/SQL procedure successfully completed.

SQL>
SQL> Select next_day(to_date(:x,'DD-MON-YYYY'), 'Monday') + (Level - 1)
  2  From   dual
  3  Connect By Level <= 5;

NEXT_DAY(
---------
03-OCT-16
04-OCT-16
05-OCT-16
06-OCT-16
07-OCT-16

Hi Connor

Thanks for the quick reply really appreciate it.

I had one issue with the code this being that when I ran it for a Monday it was always giving me the following week instead of the current week. For example when I ran it for the 26th September 2016 I was getting the results for the week commencing 3rd October. This was because the in statement was saying if a Monday or a Sunday (1,7) then you do not need to subtract any dates from the next_day code therefore this resulted in outputting week commencing 3rd October.

I changed the code to check in (6,7) so that when its run on a sat and sun it will look at the following week and when run for the other days it will move back 7 from the following Monday.

variable x varchar2(20)
exec :x := '26-SEP-2016'

select
next_day(to_date(:x,'DD-MON-YYYY'),'MON') +
case when to_number(to_char(to_date(:x,'DD-MON-YYYY'),'D')) in (6,7) then -1 else -8 end +
rownum dte
from  dual
connect by level <= 5;

I am also modified the code to accept sysdate as opposed to a variable.

SELECT to_char(NEXT_DAY(SYSDATE,'MON')
+ case when to_char(SYSDATE,'D') in (6,7) then -1 else -8 end + rownum, 'DAY DD-MM-YY') day
from  dual
connect by level <= 5;

Thanks again for the prompt and informative reply

Hi - in a previous gig, I used this :

select sysdate Today, trunc(sysdate,'day') Week_Start, trunc(sysdate,'day')+6 Week_last_Day from dual;

As documented, the 'day' format mask return the first day of the week according to the Territory in use - so, if you know for sure with a 101% degree of insurance that the database is running under a Territory where the starting day is Monday, you could use the shorter version above...

Example :

=> let´s put the database in a territory where Monday is the starting day (and adjust date format just for a better exhibition) :

SYSTEM:@XE:SQL>ALTER SESSION SET NLS_TERRITORY = "UNITED KINGDOM";

Session altered.

SYSTEM:@XE:SQL>alter session set NLS_DATE_FORMAT='DD/MM/YYYY';

Session altered.

==> the result, showing Monday as starting day :

SYSTEM:@XE:SQL>select sysdate Today, trunc(sysdate,'day') Week_Start, trunc(sysdate,'day')+6 Week_last_Day from dual;

TODAY WEEK_START WEEK_LAST_
---------- ---------- ----------
29/09/2016 26/09/2016 02/10/2016

==> and another where Monday is the starting day of the week :

SYSTEM:@XE:SQL>ALTER SESSION SET NLS_TERRITORY = Germany;

Session altered.

SYSTEM:@XE:SQL>alter session set NLS_DATE_FORMAT='DD/MM/YYYY';

Session altered.

SYSTEM:@XE:SQL>select sysdate Today, trunc(sysdate,'day') Week_Start, trunc(sysdate,'day')+6 Week_last_Day from dual;

TODAY WEEK_START WEEK_LAST_
---------- ---------- ----------
29/09/2016 26/09/2016 02/10/2016

==> now let´s indicate another Territory where Sunday (day 25 in current week) is the starting day :

SYSTEM:@XE:SQL>ALTER SESSION SET NLS_TERRITORY = Brazil;

Session altered.

SYSTEM:@XE:SQL>alter session set NLS_DATE_FORMAT='DD/MM/YYYY';

Session altered.

SYSTEM:@XE:SQL>select sysdate Today, trunc(sysdate,'day') Week_Start, trunc(sysdate,'day')+6 Week_last_Day from dual;

TODAY WEEK_START WEEK_LAST_
---------- ---------- ----------
29/09/2016 25/09/2016 01/10/2016

SYSTEM:@XE:SQL>

=> another one :

SYSTEM:@XE:SQL>ALTER SESSION SET NLS_TERRITORY = America;

Session altered.

SYSTEM:@XE:SQL>alter session set NLS_DATE_FORMAT='DD/MM/YYYY';

Session altered.

SYSTEM:@XE:SQL>select sysdate Today, trunc(sysdate,'day') Week_Start, trunc(sysdate,'day')+6 Week_last_Day from dual;

TODAY WEEK_START WEEK_LAST_
---------- ---------- ----------
29/09/2016 25/09/2016 01/10/2016


SYSTEM:@XE:SQL>

Regards,

J. Laurindo Chiappa

You forgot to subtract 5 days from the date.

SQL> variable x varchar2(10)
SQL> exec :x := '2016-09-26'

PL/SQL procedure successfully completed.

SQL> Select next_day(to_date(:x, 'YYYY-MM-DD') - 5, 'Monday') + (Level - 1)
  2  From   dual
  3  Connect By Level <= 5;

NEXT_DAY
--------
26.09.16
27.09.16
28.09.16
29.09.16
30.09.16

SQL>
SQL> variable x varchar2(10);
SQL> exec :x := '2016-10-01'

PL/SQL procedure successfully completed.

SQL> Select next_day(to_date(:x, 'YYYY-MM-DD') - 5, 'Monday') + (Level - 1)
  2  From   dual
  3  Connect By Level <= 5;

NEXT_DAY
--------
03.10.16
04.10.16
05.10.16
06.10.16
07.10.16

SQL>

This will "shift the start of the next week to saturday".

Hi guys,
Is there an elegant way of generating weeks (rather than days) starting with a given date and having week count always start with 1? I must not be doing something right with connect by...

So if I wanted, say, 8 weeks starting with 1-June, the query would produce

WK_NUM START_DATE
1 01-JUN-2020
2 08-JUN-2020
3 15-JUN-2020
4 22-JUN-2020
5 29-JUN-2020
6 06-JUL-2020
7 13-JUL-2020
8 20-JUL-2020

This gives the right weeks, but it's super clunky because it still generates days in the subquery (and I don't know how to get the weeks to start with 1 either...)

  with datelist as (
       select to_date('02-SEP-2019', 'DD-MON-YYYY') + rownum - 1 as alldays
         from dual
       connect by rownum <= 52 * 7
    )
    select alldays as start_date from datelist
    where to_char(alldays, 'D') = to_char(to_date('02-SEP-2019', 'DD-MON-YYYY'), 'D')

There's got to be a better way of doing it...
Thank you!

Hi Connor,

DOH! Thanks for that.
Just multiplying by 7, by the way, causes everything to start with the second week, so had to subtract. Final version:

select rownum as wk_num, to_date('02-SEP-2019', 'DD-MON-YYYY') -7 + rownum*7 as start_date
from dual connect by rownum <= 52 

:)

I would like to get months with respect to month number please help me