question

using a tally table (starting at n =1) SET dateformat MDY declare @startdate datetime,@enddate datetime set @startdate='01/01/2010' set @enddate='01/31/2010' SELECT @startdate + (n-1) FROM dbo.tally WHERE n-1 < DATEDIFF (dd, @startdate, @enddate) More on Tally tables [here][1] Or if you don't want to use a 'physical' tally table, utilise a cte-based one SET dateformat MDY declare @startdate datetime,@enddate datetime set @startdate='01/01/2010' set @enddate='01/31/2010' ;WITH Nbrs_4( n ) AS ( SELECT 1 UNION SELECT 0 ), Nbrs_3( n ) AS ( SELECT 1 FROM Nbrs_4 n1 CROSS JOIN Nbrs_4 n2 ), Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ), Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ), Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ), Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ) SELECT @startdate + (n-1) FROM (SELECT row_number() OVER(ORDER BY n) FROM Nbrs) Tally (n) WHERE n < DATEDIFF (dd, @startdate, @enddate) + 1 [1]: http://www.sqlservercentral.com/articles/T-SQL/62867/

I wanted to add this as a comment to Kev's answer but it was too big. This is the fastest function to create a "virtual" tally table i have ever seen. You can step forward or backward and you can step more than one step at a time. (like when you want each monday in the given range) CREATE FUNCTION [DBO].[fnNumsTable]( @pStartValue BIGINT= 1, @pEndValue BIGINT= 1000000, @pIncrement BIGINT= 1 ) RETURNS TABLE AS -- +---------------------------------------------------------------------------------------------------------------- -- ! O b j e c t : [DWH].[fnNumsTable] -- ! R e t u r n s : A table with number from startvalue to end value -- ! P a r a m e t e r s : Name DataType Description -- + ======================= ============== ================================================== -- ! @pStartValue BIGINT= 1, -- ! @pEndValue BIGINT= 1000000, -- ! @pIncrement BIGINT= 1 -- + --------------------------------------------------------------------------------------------------------------- -- ! O b j e c t i v e : Return a list of all numbers between startvalue to end value -- ! The table may be used to avoid cursors, generate a list of dates etc. -- + --------------------------------------------------------------------------------------------------------------- -- ! H i s t o r y : -- + --------------------------------------------------------------------------------------------------------------- -- ! Date Who What -- + ========== ===== ======================================================================== -- ! 2009-09-21 HAWI -- +---------------------------------------------------------------------------------------------------------------- --Select n from dbo.[fnNumsTable](1000,2000,1) --Select DATEADD(D,N,GETDATE()) from dbo.[fnNumsTable](1,365,7) RETURN( WITH BaseNum ( N ) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), L1 ( N ) AS ( SELECT bn1.N FROM BaseNum bn1 CROSS JOIN BaseNum bn2 ), L2 ( N ) AS ( SELECT a1.N FROM L1 a1 CROSS JOIN L1 a2 ), L3 ( N ) AS ( SELECT TOP ((ABS(CASE WHEN @pStartValue < @pEndValue THEN @pEndValue ELSE @pStartValue END - CASE WHEN @pStartValue < @pEndValue THEN @pStartValue ELSE @pEndValue END))/ABS(@pIncrement)+ 1) a1.N FROM L2 a1 CROSS JOIN L2 a2 ), Tally ( N ) AS ( SELECT row_number() OVER (ORDER BY a1.N) FROM L3 a1 ) SELECT ((N - 1) * @pIncrement) + @pStartValue AS N FROM Tally ); GO

[ http://itdeveloperzone.blogspot.in/2012/11/generate-list-of-dates-in-sql-server.html][1] [1]: http://itdeveloperzone.blogspot.in/2012/11/generate-list-of-dates-in-sql-server.html