Wednesday, 24 August 2016

How to generate range of Dates

There the script to generate range of dates between 1.January 2000 and 31.December next year:

DROP VIEW IF EXISTS dim.calendar_dates;
GO
/*
http://yabele.blogspot.de/2016/08/how-to-generate-range-of-dates.html
*/
CREATE VIEW dim.calendar_dates
AS
WITH [dates] AS
(
    SELECT date_value = CAST('2000-01-01' AS DATETIME)
    UNION ALL
    SELECT date_value = DATEADD(DAY, 1, date_value)
    FROM [dates]   
    WHERE date_value < CAST(YEAR(GETDATE()) + 1 AS CHAR(4)) + '-12-31'
)
SELECT *
FROM [dates]
/*
SELECT *
FROM dim.calendar_dates
ORDER BY date_value
OPTION (MAXRECURSION 0);
*/