Get all dates between two dates in SQL Server

Opting for high performance using set-based operations

Set-based operations outperform row-by-row traversing, improving query performance. Use an existing calendar table for straightforward and efficient retrieval:

SELECT CalendarDate
FROM YourCalendarTable -- It's like flipping through a calendar.
WHERE CalendarDate BETWEEN @Start AND @End;


Without a calendar table, leverage a numbers table or a sequence of integers to side-step performance dips from recursive CTEs.

Handling larger date ranges effectively

Scaling to address large date ranges requires avoiding the execution limits of recursive CTEs by leveraging a numbers table or clever techniques like the one shown here:

DECLARE @Start DATE = '2021-01-01', @End DATE = '2021-12-31'; -- Our temporal trip for a whole year!
SELECT DATEADD(DAY, nbr - 1, @Start) AS Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY c.object_id) AS nbr
      FROM sys.columns c) numbers -- The magic happens here.
WHERE nbr <= DATEDIFF(DAY, @Start, @End) + 1; -- Till the journey ends.


This yields the dates from January 1 to December 31, 2021, leveraging the system objects as a number generator, bypassing recursion.

Clarity and simplicity: Friends for life

For readability and maintainability, punt complex recursive CTEs in favor of a simple SELECT statement where feasible. Use table-valued functions (TVFs) to encapsulate date generation logic, providing reusability benefits.

Building exceptions and customizations into your queries

Avoid infinite loops

For wide date ranges, the recursion limit of recursive CTEs may halt your journey. Set OPTION (MAXRECURSION 0) at the query end to allow unlimited recursion - unrestricted time travels!

Custom date formats

In case formatted strings suit your style better than date data types, use CONVERT or FORMAT to get customized date displays:

SELECT FORMAT(Date, 'yyyy-MM-dd') AS FormattedDate
FROM DateSeq; -- Now, dates exactly to your liking!


Non-recursive spaces

Where recursion is not preferred, fall back on a while loop or tally table for date generation:

DECLARE @DateList TABLE (Date DATE); -- Our table to store the time travel journey.
DECLARE @Current DATE = @Start; -- We're starting from the beginning.
WHILE @Current <= @End -- Let's not break the time-space continuum.
BEGIN
    INSERT INTO @DateList VALUES (@Current); -- One more day.
    SET @Current = DATEADD(DAY, 1, @Current); -- Next step on the bridge.
END
SELECT Date FROM @DateList; -- Here's your journey!


This loop traverses the date range, depositing each date into the @DateList table avoiding recursion.