Oracle SQL: Return a distinct list of days when inputting a min and max of a date field-CodePudding

Is the following the correct way to get a distinct list of days for a date range (min and max of a date field) I intend to create a sql view out of this:

with range as (
  select min(date) start_date,
       max(date) end_date
  from table
)
  select start_date   level - 1 AS "DATE",
  extract(month from start_date   level - 1) AS "MONTH",
  extract(year from start_date   level - 1) AS "YEAR"
  from range
  connect by level <= (
     trunc(end_date) - trunc(start_date)   1
  );

CodePudding user response：

Do you really need to create a DATE table when you can generate one on the fly if needed


ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';


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-07-01',
DATE '2022-07-31')) c