The task is to create SQL query that will produce a date sequence starting from @startDate till @endDate including, i.e. 2017-01-01, 2017-01-02, 2017-01-03 and so on.
There is a couple of obvious solutions.
1. DATEADD() with integer sequence where the integer sequence is an increment
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @startDate date = CAST('2014-04-01' AS date), @endDate date = CAST(GETDATE() AS date); SELECT DATEADD(day, number - 1, @startDate) AS [Date] FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY n.object_id ) FROM sys.all_objects n ) S(number) WHERE number <= DATEDIFF(day, @startDate, @endDate) + 1; |
Please take care how many records are in sys.all_objects, it won’t generate a long sequence. If you need more, use a self cross join as shown inĀ Generating Integer Sequence With SQL Query.
2. Recursive query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE @startDate date = CAST('2014-04-01' AS date), @endDate date = CAST(GETDATE() AS date); WITH Date_CTE AS ( SELECT [Date] = @startDate UNION ALL SELECT [Date] = DATEADD(day, 1, [Date]) FROM Date_CTE WHERE [Date] < GETDATE() ) SELECT [Date] FROM Date_CTE OPTION (MAXRECURSION 0) |
The last option is very important because SQL Server limits the recursion level to 100 by default.