How to generate list of dates --- ie every day for the past 2 years?

Question

We're using a business intelligence system and need to load a list of dates so we can flag them as 'last week' or 'last 12 months' or some dynamic value.

I'm wondering what's the simplest way to virtually generate a table that simply lists dates in one column, literally every date from '2014-01-01' to the current date (the other columns I can use a formula from there). Actually even appending future dates on for a year would probably be useful as well.

Now yes I can grab distinct dates from another random fact table that has thousands of entries, but that seems sloppy and is creating a dependency where there really shouldn't be one.

Answer 1

Using pure T-SQL, you can do the following:

;WITH cte AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS [Incrementor]
  FROM   [master].[sys].[columns] sc1
  CROSS JOIN [master].[sys].[columns] sc2
)
SELECT DATEADD(DAY, cte.[Incrementor], '2014-01-01')
FROM   cte
WHERE  DATEADD(DAY, cte.[Incrementor], '2014-01-01') < GETDATE();

Or, I also wrote a SQLCLR function to make this a little easier, which is available in the Free version of the SQL# library:

SELECT [DatetimeVal]
FROM   [SQL#].[Util_GenerateDateTimeRange]('2014-01-01', GETDATE(), 1, N'day');

If you want the ending date to be in the future, just replace the GETDATE() in either query.

Answer 2

Try this.

https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function/1378788#1378788

You don't need a different table, although if you have one it could help.