Search code examples
sql-serverdatetemp-tablesprocedure

How to get a table of dates between x and y in sql server 2005

I just want a quick way (and preferably not using a while loop)of createing a table of every date between date @x and date @y so I can left outer join to some stats tables, some of which will have no records for certain days in between, allowing me to mark missing days with a 0

Solution

  • Strictly speaking this doesn't exactly answer your question, but its pretty neat.

    Assuming you can live with specifying the number of days after the start date, then using a Common Table Expression gives you:

    WITH numbers ( n ) AS (
        SELECT 1 UNION ALL
        SELECT 1 + n FROM numbers WHERE n < 500 )
    SELECT DATEADD(day,n-1,'2008/11/01') FROM numbers
    OPTION ( MAXRECURSION 500 )