T-SQL: Generate a list of dates
SQL Server 2005 | SQL Server | SQL | Common Table Expression | T-SQL | List of dates
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)

Read on