How to generate the first day of the week, the last day of the week and week number between two dates in Oracle

Question

I would like to insert in table :

• the first day of the week starting from Monday.
• the last day of the week as Sunday.
• the WEEK number => (1-52 or 1-53) based on the ISO standard.

First i tried to select first day, the last day and week number for specific date and it's works :

WITH ranges AS
(
SELECT to_date('29-10-2012', 'dd-MM-yyyy') AS DATE_TEST FROM DUAL
)
SELECT DATE_TEST "DATE",
TO_CHAR( NEXT_DAY( TRUNC(DATE_TEST) , 'SUNDAY' )) "WEEK END DATE",
TO_CHAR(TO_DATE(DATE_TEST,'DD-MON-YYYY'),'WW')+1 "WEEK NUMBER"
FROM ranges ;

But now i would like to display this data between two dates, but i get result only for the start_date. someone can help please.

after, when all good i will insert all in the table.

Thanks

WITH ranges AS(
   select to_date('29-OCT-2012', 'dd-MM-yyyy') START_DATE, 
       to_date('31-DEC-2016', 'dd-MM-yyyy') END_DATE 
from  dual 
)
SELECT START_DATE "DATE",
TO_CHAR( NEXT_DAY( TRUNC(START_DATE) , 'SUNDAY' )) "WEEK END DATE",
TO_CHAR(TO_DATE(START_DATE,'DD-MON-YYYY'),'WW')+1 "WEEK NUMBER"
FROM ranges ;

Answer 1

Format WW returns 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, see Datetime Format Elements

In order to get the week number according to ISO-8601 standard use format IW. I would suggest like this:

WITH ranges AS(
    SELECT 
        DATE '2012-10-29' START_DATE,
        DATE '2016-12-31' END_DATE 
    FROM dual 
)
SELECT 
    START_DATE, END_DATE,
    TRUNC(START_DATE + 7*(LEVEL-1), 'IW') AS Week_Start_Date,
    TRUNC(START_DATE + 7*(LEVEL-1), 'IW') + 6 AS Week_End_Date,
    TO_CHAR(TRUNC(START_DATE + 7*(LEVEL-1)), 'IYYY-"W"IW') WEEK_NUMBER
FROM ranges
CONNECT BY START_DATE + 7*(LEVEL-1) <= END_DATE;