Sometimes we can be working and we write or find a ridiculously simple bit of code that we know will be useful in the future, then we just go ahead and plain forget all about it. When I find such stuff I like to stick it on my blog so that I know where to find it in the future and also on the off chance that someone else might find it useful. Here is one such example.
The following bit of code uses a common table expression (CTE) to generate a contiguous list of dates in SQL Server.
1 with mycte as
2 (
3 select cast('1900-01-01' as datetime) DateValue
4 union all
5 select DateValue + 1
6 from mycte
7 where DateValue + 1 < '2050-12-31'
8 )
9 select DateValue
10 from mycte
11 OPTION (MAXRECURSION 0)
Nice 'n' easy! If you want a different date range simply change the dates in the query. Of course, you can easily extend this to produce all the attributes of a day that you may require in a Time dimension table - maybe/hopefully someone else might do that for me.
Before CTEs turned up in SQL Server 2005 you would have needed procedural code to do this.
By the way, I came up with this code after my colleague James Pipe found the equivalent code for Oracle. He's promised that his first ever blog post will be on this subject (I'm trying to goad him into writing it).
-Jamie
N.B. I used J.T. Leigh's excellent Copy Source as HTML tool in order to write this blog post.
UPDATE: James has now posted his like-for-like posting. You can see it here: http://blogs.conchango.com/tags/List+of+dates/default.aspx