Get a list of dates between two dates

sql

temp-tables

date-range

performance-optimization

byAlex Kataev·Oct 7, 2024

Create a sequence of dates using a recursive CTE in SQL. Increment daily from start date until reaching the end date:

WITH Dates AS (
    SELECT CAST('2023-01-01' AS DATE) AS Date -- Kickoff date
    UNION ALL
    SELECT DATEADD(day, 1, Date) -- Incrementing the date
    FROM Dates
    WHERE Date < '2023-01-10' -- Touchdown date
)
SELECT Date
FROM Dates
OPTION (MAXRECURSION 0); -- Ensure recursion till the end of time (or the end of dates)

Replace '2023-01-01' and '2023-01-10' with your dates. This gives a continuous date range.

Boost speed with temporary tables

For larger datasets, a temporary table could enhance performance. This stores every date and avoids CTE:

CREATE TABLE #DateTable (Date DATE); -- Creating our sand box

DECLARE @StartDate DATE = '2023-01-01', @EndDate DATE = '2023-01-10'; -- Marking the boundaries

WHILE @StartDate <= @EndDate
BEGIN
    INSERT INTO #DateTable VALUES (@StartDate); -- Add to sand box
    SET @StartDate = DATEADD(day, 1, @StartDate); -- Move forward a day
END

SELECT Date FROM #DateTable; -- Plucking out the date pearls

DROP TABLE #DateTable; -- Cleaning up after playing

Useful when you need to join this with other datasets or perform aggregations.

Balancing performance and alternatives

Knowing the performance trade-off is crucial when dealing with large date ranges or multiple units. MAXRECURSION in SQL Server regulates the recursion depth, while MySQL increments user variables within a SELECT statement to generate dates.

Visit MySQL User Variables for granular details on user variable operations.

Tackle nulls and use date extraction

When joining a date table using left outer join, beware of nulls that may disrupt data consistency. Use the DATE function to extract the date part from datetime values:

SELECT COALESCE(t.Date, d.Date) as Date
FROM #DateTable d
LEFT JOIN YourDataTable t ON d.Date = DATE(t.DateTimeColumn); -- Bringing date partners for a dance

Recursive fun with MySQL

Utilize recursive CTEs in MariaDB >= 10.3 and MySQL >= 8.0 to generate a list of dates smoothly:

WITH RECURSIVE DateRange AS (
    SELECT '2023-01-01' AS Date -- Inception date
    UNION ALL
    SELECT DATE_ADD(Date, INTERVAL 1 DAY) -- Forwarding the date wheel
    FROM DateRange
    WHERE Date < '2023-01-10' -- The date finish line
)
SELECT Date
FROM DateRange; -- Voila! Your date sequence

In MySQL, set the delimiter to specify blocks of procedure code, just to be on the safer side.

Path to mastery: Comprehensive solutions

Whether you prefer a minimalist approach with a recursive CTE or a more robust solution using a temp table, you've got options. Tailor the strategy to your specific needs for seamless date sequence creation.

Manage temporal tables effectively

Ensure your procedures capture both start and end dates for a complete date range. This is crucial for accurate data analysis. Efficiently designed date tables cater to your specific joining needs with minimal overhead.