Business Intelligence Blogs

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.

Generate a Date Table via Common Table Expression (CTE)

  • 15 May 2015
  • Author: DustinRyan
  • Number of views: 9535

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

    union all 

    Select dateadd(day, 1, [Date])
    from dates
    where [Date] <= '2020-12-31' -- Put the end date here 

select [Date]
from dates
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.

Categories: Big Data
Rate this article:


Other posts by DustinRyan

Please login or register to post comments.