View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.
Occasionally I find myself needing to generate a small table with a list of dates for various queries I may be running. To do this, I usually leverage the Date dimension since I do most of my work in BI environments with a traditional data warehouse. But if you don’t have access to a Date dimension table, you can quickly generate a date table using the following CTE:
;with dates ([Date]) as (
Select convert(date,'2000-01-01') as [Date] -- Put the start date here
Select dateadd(day, 1, [Date])
where [Date] <= '2020-12-31' -- Put the end date here
option (maxrecursion 32767) -- Don't forget to use the maxrecursion option!
Don’t forget to use the option (maxrecursion 32767) in your query to allow your CTE to surpass the default maximum recursion limit of 100. The maximum value you can use with option maxrecursion is 32767, which should allow you to generate a date table with with almost 90 years worth of dates, which for my purposes is usually plenty! Anyways, I hope you find this helpful.
Other posts by DustinRyan