4

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.

2 Answers 2

6

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.

1
  • The SQLCLR function sounds interesting - do you have a feel for how it performs compared to the TSQL alternatives ?
    – Moe Sisko
    Commented May 28, 2021 at 0:43
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.