DECLARE @start datetime, @numberofyears int
SET @start = '1900-01-01'
SET @numberofyears = 180
;WITH a1(col)
AS (
SELECT NULL
UNION ALL
SELECT NULL
), a2(col)
AS (
SELECT NULL FROM a1 x1 CROSS JOIN a1 x2
), a3(col)
AS (
SELECT NULL FROM a2 x1 CROSS JOIN a2 x2
), a4(col)
AS (
SELECT NULL FROM a3 x1 CROSS JOIN a3 x2
), a5(col)
AS (
SELECT NULL FROM a4 x1 CROSS JOIN a4 x2
), nums(num)
AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 FROM a5
), dates(datetime)
AS (
SELECT DATEADD(day, num, @start) FROM nums WHERE DATEADD(day, num, @start) < DATEADD(year, @numberofyears, @start)
)
SELECT YEAR(datetime) * 10000 + MONTH(datetime) * 100 + DAY(datetime) AS DATEKEY, datetime FROM dates
