How to generate a date sequence in T-SQL

If you ever had the pleasure to run queries over an access log I bet you had a desperate need for a date sequence to map query results to.

There are plenty of pages out there discussing the best way to create and populate number tables. After reading this great post on stack overflow I came up with a slight variation of one of the solutions mentioned there, like so:

This returns 273 records with dates from  Jan, 1 2014 to Sep, 30 2014.

The advantages of this solution are:

  • no need to recur to a pre-generated numbers table
  • is simple and concise and can easily be used as a subquery
  • upper limit of number generator depends only on the size of master..spt_values – table (which contains 2047 records at minimum, so the cartesian product gives at least a sequence up to 4.194.304). If you want to know more about spt_values, see this post here.

Of course, it is tedious to repeat yourself over and over again. Better you wrap it in user-defined functions.

First, the number generator:

Then the date sequence generator, which is building on the number generator:

This function has two parts – enclosed in if – statements – generating sequences for months and for days.

Now, it’s very easy to get a time period in days with:

at least within the date range available for a SQL Server DATETIME type going from Jan, 1 1753 to Dec, 31 9999 (my solution is missing the last day due to the DATEADD operation which would result in an overflow error).

If you want to dig deeper, read:

  1. Aaron Bertrands article Generate a set or sequence without loops is sort of a reference to the topic. He also has a look on performance issues.
  2. This answer on stack overflow presents an almost comprehensive overview of various methods to create and populate a numbers table.