creating_date_ranges

SQL Server

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 DateAdd(DAY,
               -ROW_NUMBER() OVER (ORDER BY (SELECT 100)),
               DateAdd(DAY,   DateDiff(DAY,   0, GetDate()), 0)) AS TheDate
  FROM sys.all_objects
)
SELECT TheDate
  FROM date_list
 WHERE TheDate BETWEEN '02-FEB-17'
                   AND '26-MAR-18'
 ORDER BY TheDate
;

Oracle

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
;
  • creating_date_ranges.txt
  • Last modified: 2021/01/12 21:08
  • by 127.0.0.1