How to list weeks between two dates In Oracle Sql query

In my recent Infosys interview, i faced one question to solve Oracle sql query "How to list weeks between two dates"

I  have one requirement

START_DATE : 03/01/2018
END_DATE : 31/01/2018

I need a query which will list all the weeks starting date and end date between these two dates like this:

StartWeek   EndWeek
03/01/2018  04/01/2018
07/01/2018  11/01/2018
14/01/2018  18/01/2018
21/01/2018  25/01/2018
28/01/2018  31/01/2018

Answer:

CREATE OR REPLACE PACKAGE week_pkg
AS
    TYPE week_rec IS RECORD (start_week DATE, end_week DATE);
    TYPE week_tab IS TABLE OF week_rec;
    FUNCTION get_weeks (p_start_date DATE, p_end_date DATE) RETURN week_tab PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY week_pkg
AS
    FUNCTION get_weeks (p_start_date DATE, p_end_date DATE) RETURN week_tab PIPELINED
    IS
        v_date DATE;
        v_week_rec week_rec;
    BEGIN
        v_date := p_start_date - 7;

        LOOP
             v_week_rec.start_week := NEXT_DAY(v_date, 'SUNDAY');
             IF v_week_rec.start_week < p_start_date THEN
                 v_week_rec.start_week:= p_start_date;
             END IF;

             v_week_rec.end_week := NEXT_DAY(v_date, 'THURSDAY');


             IF v_week_rec.end_week >= p_end_date THEN
                 v_week_rec.end_week := p_end_date;
                 PIPE ROW (v_week_rec);
                 EXIT;
             ELSIF v_week_rec.end_week <= p_start_date THEN
                 v_week_rec.end_week := NEXT_DAY(v_week_rec.start_week, 'THURSDAY');
             END IF;

             v_date := v_week_rec.end_week;

             PIPE ROW (v_week_rec);
         END LOOP;
    END;
END;
/

SELECT *
  FROM table(week_pkg.get_weeks(to_date('03-JAN-2018', 'DD-MON-YYYY'), to_date('31-JAN-2018', 'DD-MON-YYYY')));

Or

You can use a recursive sub-query factoring clause:

WITH input_dates ( start_date, end_date ) AS (
  SELECT DATE '2018-01-03', DATE '2018-01-31'
  FROM   DUAL
),
valid_start_date ( start_date, end_date ) AS (
  SELECT CASE
         WHEN start_date - TRUNC( start_date, 'IW' )
                IN (
                  0, -- Monday
                  1, -- Tuesday
                  2, -- Wednesday
                  3, -- Thursday
                  6  -- Sunday
                )
         THEN start_date
         ELSE NEXT_DAY( start_date, 'SUNDAY' )
         END,
         end_date
  FROM   input_dates
),
dates ( start_week, end_week, end_date ) AS (
  SELECT start_date,
         LEAST( NEXT_DAY( start_date, 'THURSDAY' ), end_date ),
         end_date
  FROM   valid_start_date
  WHERE  start_date <= end_date
UNION ALL
  SELECT NEXT_DAY( start_week, 'SUNDAY' ),
         LEAST( end_week + INTERVAL '7' DAY, end_date ),
         end_date
  FROM   dates
  WHERE  NEXT_DAY( start_week, 'SUNDAY' ) <= end_date
)
SELECT start_week, end_week
FROM   dates;.