Recently a developer came to me seeking my help in generate list dates between date range for a report. Being a .Net developer he said that one solution he had is to do row by row processing either by using while loop or a cursor. We exchanged smiles and he understood what I had in my mind..
I came up with 2 solutions for this problem. One is to use a tally table and another one using a recursive CTE.
DECLARE @dt1 Datetime='2012-08-01' DECLARE @dt2 Datetime='2012-09-01' ;WITH ctedaterange AS (SELECT [rn]=Row_number() OVER( ORDER BY (SELECT NULL)) FROM sys.objects a CROSS JOIN sys.objects b CROSS JOIN sys.objects c CROSS JOIN sys.objects d) SELECT Dateadd(dd, rn, @dt1) FROM ctedaterange WHERE [rn] <= Datediff(dd, @dt1, @dt2)
DECLARE @dt1 Datetime='2012-08-01' DECLARE @dt2 Datetime='2012-09-01' ;WITH ctedaterange AS (SELECT [Dates]=@dt1 UNION ALL SELECT [dates] + 1 FROM ctedaterange WHERE [dates] + 1<= @dt2) SELECT [dates] FROM ctedaterange OPTION (maxrecursion 0)
Method 1 performs a bit better than method 2 for large result set.
Do let me know if you have a different way to do this.