Dynamically generating a table of dates in Microsoft SQL (Part Deux!)

Yesterday I wrote a blog entry talking about dynamically generating a table of dates in MSSQL 2005. My original solution involved common table expressions (CTE) and involved recursion. While there's nothing wrong with using recursion this way, I mainly was using recursion to simplify the UDF code into a single SQL expression.

However, the more I started thinking about the problem last night, I decided I really needed to revise things a bit further. I decided I really needed to have the query return both a start and end date for each row in the recordset—as this would make it much easier to things such as find all the orders placed during each hour of the day.

So, this morning I came in and re-wrote my query and optimized a few things. The first thing I did was remove the recursion, so this UDF should now work in SQL Server 2000 as well as SQL Server 2005. It's important to note that you could do the same thing with a physical table, but for my uses my date range table generally would only include at most a few dozen rows and there's not enough of a performance difference to warrant the pain of maintaining a table of every date and possible combination you may want to return aggregate results over.

The key changes are the query now returns both a start_date and end_date for each row, there's no no recursion and for dateparts that don't involve time intervals, the UDF will strip out the time automatically. This makes it very easy to use with the getdate() or getutcdate() functions in SQL Server.

Now I can easily generate a table of date ranges for the past 30 days:

The introduction of the start_date and end_date columns also changes the syntax of the queries a bit. To take use of the start_date and end_date columns, you'll want to join rows based on whether the time falls between the start_date and end_date columns.

So, if we wanted to find out the total number of orders placed in each our of our business day (from 8am until 6pm) we'd use a query like this:

select/*
we need to add all the order counts together, since there will be multiple rows
for each match found within our start_date and end_date range
*/
d.start_date as OrderDate, sum(isnull(o.OrderCount, 0)) as OrderCount
from-- create a table with hour intervals between 8am and 5pm dbo.generateDateTable('1-6-09 8:00:00', '1-6-09 17:00:00', 'hour', 1) as d
leftouterjoin-- generate an aggregate count of orders within our timeframe (
select
OrderDate, count(OrderId) as OrderCount
fromOrdergroupby
OrderDate
) as o
on-- join each match based if they fit within our timeframe o.OrderDate between d.start_date and d.end_date
-- we need to by our start_date for our aggregate countgroupby
d.start_date
orderby
d.start_date

This would produce a resultset that might look like this:

Here's what our recordset would look like for orders placed on Jan 6, 2009:

As you can see, our aggregate query is showing the correct results for hours broken down by hour for this day.

If you have any other tips or suggestions, I'd love to hear them.

This approach might not be the best approach for every problem, but it'll work well for when you have date/time ranges that are very dynamic and are limited to a pretty small resultset (under a few hundred rows.) This works perfectly for creating records sets of the last few years broken into quarters, or the past year broken down by month, etc.

Are you using the example you posted exactly? I don't have quick access to a MSSQL 2000 at the moment, but it should work fine with dynamic datetime functions. I'm using the exact syntax above in a query I've been running. The function just requires a datetime object.

From your error message, it looks like maybe you don't have the correct number of closing parenthesis in your source code (but the example you posted above is good.)

I get the same error Kubilay does with dynamic datetime functions IF I run the generateDateTable() function from another database. This happens even if I prefix the function with the name of the database in which I've stored it. This does NOT happen when static values are used. I'm using the examples from the body of Dan's post: