This is an old revision of the document!

WITH date_range AS (
SELECT DateAdd(DAY, a.a + (10 * b.a) + (100 * c.a), DateAdd(DAY, DateDiff(DAY, 0, GetDate()), 0) - 365) AS TheDate
  FROM (
                   (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS A
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS B
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS C
       )
WHERE 1=1
--AND a.a + (10 * b.a) + (100 * c.a) < 365                                                              -- Yesterday and before
--AND DateAdd(DAY, a.a + (10 * b.a) + (100 * c.a), DateAdd(DAY, DateDiff(DAY, 0, GetDate()), 0) - 365) <  DateAdd(DAY, DateDiff(DAY, 0, GetDate()), 0)
--AND a.a + (10 * b.a) + (100 * c.a) <= 365                                                             -- Today and before
--AND DateAdd(DAY, a.a + (10 * b.a) + (100 * c.a), DateAdd(DAY, DateDiff(DAY, 0, GetDate()), 0) - 365) <= DateAdd(DAY, DateDiff(DAY, 0, GetDate()), 0)
--AND a.a + (10 * b.a) + (100 * c.a) >= 365                                                             -- Today and onwards
--AND DateAdd(DAY, a.a + (10 * b.a) + (100 * c.a), DateAdd(DAY, DateDiff(DAY, 0, GetDate()), 0) - 365) >= DateAdd(DAY, DateDiff(DAY, 0, GetDate()), 0)
--AND a.a + (10 * b.a) + (100 * c.a) > 365                                                              -- Tomorrow and onwards
--AND DateAdd(DAY, a.a + (10 * b.a) + (100 * c.a), DateAdd(DAY, DateDiff(DAY, 0, GetDate()), 0) - 365) >  DateAdd(DAY, DateDiff(DAY, 0, GetDate()), 0)
)
SELECT TheDate
  FROM date_range
WHERE TheDate BETWEEN DateAdd(YEAR, -1, DateDiff(DAY,   0, GetDate())) -- 1 year ago
                   AND DateAdd(DAY,  DateDiff(DAY,   0, GetDate()), 0)  -- Today
ORDER BY TheDate DESC
;
 
 
      WITH date_list AS (
        SELECT TRUNC(SYSDATE) - rownum proc_date
          FROM dual
       CONNECT BY LEVEL < 365
      )
      SELECT proc_date
        FROM date_list
    ORDER BY proc_date
    ;
 
 
  FUNCTION get_date_range
          (process_id IN NUMBER
          ) RETURN SYS_REFCURSOR
  IS
    c SYS_REFCURSOR;
  BEGIN
    OPEN c FOR
      WITH date_list AS (
        SELECT TRUNC(SYSDATE) - rownum proc_date
          FROM dual
       CONNECT BY LEVEL < 365
      )
      SELECT proc_date
        FROM date_list
    ORDER BY proc_date
    ;
 
    RETURN c;
  END;
 
 
 
-- Past
      WITH date_list AS (
        SELECT TRUNC(SYSDATE) - rownum proc_date
          FROM dual
       CONNECT BY LEVEL < 366 -- How many days to go back
      )
      SELECT proc_date
        FROM date_list
    ORDER BY proc_date
    ;
 
-- Future
      WITH date_list AS (
        SELECT TRUNC(SYSDATE) + rownum proc_date
          FROM dual
       CONNECT BY LEVEL < 366 -- How many days to go forward
      )
      SELECT proc_date
        FROM date_list
    ORDER BY proc_date
    ;
 
-- Range
    WITH date_list AS (
        SELECT TRUNC(SYSDATE) - rownum proc_date
          FROM dual
       CONNECT BY LEVEL < 1000
      )
      SELECT proc_date
        FROM date_list
       WHERE proc_date BETWEEN '02-FEB-17'
                           AND '26-MAR-18'
    ORDER BY proc_date
    ;
 
 
-- Range (past)
    WITH date_list AS (
        SELECT TRUNC(SYSDATE) - rownum proc_date
          FROM all_tab_cols -- Just use a big table         
      )
      SELECT proc_date
        FROM date_list
       WHERE proc_date BETWEEN '02-FEB-17'
                           AND '26-MAR-18'
    ORDER BY proc_date
    ;