This came handy, when i wanted to get all dates of the current month when joining with mutiple tables… and not all the tables had all dates of the month
;WITH
CTE0 AS(SELECT 1 AS c UNION ALL SELECT 1),
CTE1 AS(SELECT 1 AS c FROM CTE0 AS A, CTE0 AS B),
CTE2 AS(SELECT 1 AS c FROM CTE1 AS A, CTE1 AS B),
CTE3 AS(SELECT 1 AS c FROM CTE2 AS A, CTE2 AS B),
SeqNumbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS SeqNumber FROM CTE3)
SELECT SeqNumber,CONVERT(VARCHAR(12),(GETDATE()-(DAY(GETDATE())-SeqNumber)),101) AS ReportingDate
FROM
SeqNumbers
WHERE SeqNumber <= DAY(GETDATE())
