====== 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
;