Generate Dates between date ranges

Easy on SQL 2005+; easier if you have a numbers or tally table. I faked it below:

DECLARE @StartDate DATE = '20110901'  , @EndDate DATE = '20111001'SELECT  DATEADD(DAY, nbr - 1, @StartDate)FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr          FROM      sys.columns c        ) nbrsWHERE   nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)

If you have a tally table, replace the subquery with the table. No recursion.

Try this if you are using SQL Server 2005 or newer:

WITH Dates AS (        SELECT         [Date] = CONVERT(DATETIME,'09/01/2011')        UNION ALL SELECT         [Date] = DATEADD(DAY, 1, [Date])        FROM         Dates        WHERE         Date < '10/10/2011') SELECT [Date]FROM Dates OPTION (MAXRECURSION 45)

A good example of cool stuff you can do with a CTE.

-- Declarations

DECLARE @dates TABLE(dt datetime)    DECLARE @dateFrom datetimeDECLARE @dateTo datetimeSET @dateFrom = '2001/01/01'SET @dateTo = '2001/01/12'

-- Query:

WHILE(@dateFrom < @dateTo)BEGIN   SELECT @dateFrom = DATEADD(day, 1,@dateFrom)   INSERT INTO @dates    SELECT @dateFromEND

-- Output

SELECT * FROM @dates