3

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.

4

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
    May 28 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.

-1

Updated Code for Generating dates for next 20 days from today

;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 convert(VARCHAR,DATEADD(DAY, cte.[Incrementor], GETDATE()+2),103) as [Date]
FROM   cte
WHERE  DATEADD(DAY, cte.[Incrementor], GETDATE()-20) < GETDATE();
4
  • 2
    Why is this an improvement over Solomn's answer? Why the +2? Why the conversion to VARCHAR? Mar 30 '19 at 8:29
  • please check the output... if you don't need conversion, please ignore... Apr 18 '19 at 7:27
  • OK. I can see the code, your query will produce some dates in a specific output. Why is that wanted? I don't see it in the question. And it seems you are produicng only 20 dates and not what the question asks. It would good if you update the answer and explain what the code does and how that helps. Apr 18 '19 at 7:38
  • Sure..... I updated this snippet for my colleague, he needs to generate next 20 days for his calendar... Apr 18 '19 at 9:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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