Skip to Main Content

SQL & PL/SQL

Creating range of dates for user using a function

BeefStuJun 28 2022

 have the following table and function is there a way I can create dates for each user for the dates specified?

I know the function works perfectly. I don't know how to combine it with the username.

CREATE TABLE table_name (Members, Start_DT, End_DT) AS
SELECT 'John',  DATE '2022-04-03', DATE '2022-04-10' FROM DUAL UNION ALL
SELECT 'Sam', DATE '2022-06-12', DATE '2022-06-24' FROM DUAL UNION ALL
SELECT 'Jane', DATE '2022-05-24', DATE '2022-06-01' FROM DUAL;
CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
/

CREATE OR REPLACE FUNCTION generate_dates_pipelined(
  p_from IN DATE,
  p_to   IN DATE
)
  RETURN nt_date PIPELINED DETERMINISTIC
IS
  v_start DATE := TRUNC(LEAST(p_from, p_to));
  v_end   DATE := TRUNC(GREATEST(p_from, p_to));
BEGIN
  LOOP
    PIPE ROW (v_start);
    EXIT WHEN v_start >= v_end;
    v_start := v_start + INTERVAL '1' DAY;
  END LOOP;
  RETURN;
END generate_dates_pipelined;
/

SELECT
        c.COLUMN_VALUE 
  FROM   
TABLE(generate_dates_pipelined(DATE '2022-04-01',
DATE '2022-06-30')) c
This post has been answered by Frank Kulash on Jun 28 2022
Jump to Answer
Comments
Post Details
Added on Jun 28 2022
3 comments
61 views