Sometimes we need a list of dates to run queries against or to populate other values. A common approach I've seen to this is for someone to create a table in their database that just lists out the dates for every day in a range of years. The problem is the table eventually runs out of dates and has to be repopulated as we move forward through time. One solution, obviously, is to just put a ridiculous number of dates in the table. Another solution, which I present here, is to have a self-populating view that does not require an underlying table. We can use recursion to continuously generate dates from a start date to an end date of our choosing. And, by using the GETDATE() function in SQL Server, this date range will move along with us as we move through time. This means if we want the date range can start from today's date of the previous year to today's date two years from now and the dates will always automatically adjust from one day to the next. Check it out.
CREATE VIEW [dbo].[vDimTime] AS
WITH BaseDays (TimeKey, [ID]) AS ( SELECT CAST('20030101'as DATE) AS [TimeKey], 1 AS [ID] UNION ALL SELECT DATEADD(DAY, 1, [TimeKey]), [ID] + 1 FROM BaseDays WHERE [TimeKey] < CAST(DATEADD(year, 2, GETDATE()) as Date) ),
AllDays (TimeKey, [ID]) AS (
SELECT Timekey, [ID] from BaseDays UNION SELECT CAST('17530101'as DATE) AS [TimeKey], (SELECT Max([ID]) FROM BaseDays) + 1
AS [ID] UNION SELECT CAST('19000101'as DATE) AS [TimeKey], (SELECT Max([ID]) FROM BaseDays) + 2 AS [ID] )
SELECT ID, TimeKey FROM AllDays
Notice the BaseDays uses recursion to create all the dates in a list from January 1, 2003 all the way to the current date plus 2 years using DATEADD and GETDATE(). This means today's date which is, as of this writing, 1/8/2017 will have 2 years added to it so the last date created in the list will be 1/8/2019. A year from today, in 2018, the last date in the list created will be 1/8/2020. We can use GETDATE() to create the base case for the recursive statement so rather than starting in 2003, we can start from two years, for example, prior the current date; therefore, the starting date will also move with us.
One other jewel I threw in is how to add dates outside the range to the list. In the AllDays part of the Common Table Expression (CTE) I added two dates outside the range. So by calling Alldays we get our list plus the two extra dates.
Note that the code above creates a view. Some of you may be asking, "What about OPTIONS (MAXRECURSION 0) so the recursion won't bomb after 100 iterations?"
Simple, create the view as you see above. However, when calling the view, do so with the MAXRECURSION option. If you try to create the view with OPTION (MAXRECURSION 0) in it, you'll get an error.
SELECT ID, TimeKey FROM vDimTime OPTION (MAXRECURSION 0)
Now you have a list of dates you can work with for populating other tables or running queries against.
Note that you must use the CREATE VIEW command to create the view. Using the New View GUI command will generate an error.
Need help working with dates in your database? Maybe we can help!