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
;