Generate a Date Table via Common Table Expression (CTE)

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.

Advertisements

One thought on “Generate a Date Table via Common Table Expression (CTE)”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s