How to get a list of dates between two dates in SQL?

Problem

You need to retrieve a list of dates between two specified dates.

Input

First, let’s create a table and insert some sample data into it.

For this example, the table contains two date ranges: Jan 1 to Jan 5 and Feb 1 to Feb 3 of 2023.

Try Hands-On: HERE

Source Tables: Gist

Desired Solution

Solution 1:

Using Recursive Approach

WITH RECURSIVE DateSeries AS (
    SELECT startDate AS dateValue
    FROM DateRange
    UNION ALL
    SELECT DATE_ADD(dateValue, INTERVAL 1 DAY)
    FROM DateSeries
    WHERE DATE_ADD(dateValue, INTERVAL 1 DAY) <= (SELECT endDate FROM DateRange WHERE DATE_ADD(dateValue, INTERVAL 1 DAY) BETWEEN startDate AND endDate)
)
SELECT DISTINCT dateValue
FROM DateSeries
ORDER BY dateValue;

Explanation:

• The recursive CTE starts by selecting the startDate from the DateRange table.

• In the subsequent recursive part of the CTE (UNION ALL), we use DATE_ADD to add one day to the current dateValue
  .-
  The WHERE clause inside the recursive part ensures that the date addition only proceeds as long as the new date is still between the startDate and endDate of any row in the DateRange table

• .
  Finally, outside the CTE, we select the distinct dates to eliminate any duplicate date values and order the result by dat
  ble.

Solution 2:

An alternative method to retrieve a list of dates between two dates in MySQL involves using a helper table (or number series). This approach uses a table that simply has a sequence of numbers, which we can use to generate a range of dates.

First, we’ll create a helper table named NumberSeries:

CREATE TABLE NumberSeries (n INT);

-- Let's insert a series of numbers. This will cover a range for up to 1000 days. You can increase it if needed.
DELIMITER ;
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 1000 DO
        INSERT INTO NumberSeries (n) VALUES (i);
        SET i = i + 1;
    END WHILE;
END;
DELIMITER ;

Solution:

SELECT DATE_ADD(dr.startDate, INTERVAL ns.n DAY) AS dateValue
FROM DateRange dr
JOIN NumberSeries ns
ON DATE_ADD(dr.startDate, INTERVAL ns.n DAY) BETWEEN dr.startDate AND dr.endDate
ORDER BY dateValue;

Explanation:

• We use a JOIN between the DateRange table and the NumberSeries table.

• The DATE_ADD function with the ns.n value from the NumberSeries table is used to add days to the startDate.
  The BETWEEN condition in the ON clause ensures that the generated date falls within the range of startDate and endDate for each row in the DateRange table.

• Lastly, we order the results by the generated date (dateValue).

• By using this solution, you can retrieve the list of dates between the start and end dates for each row in the DateRange table.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to access previous row value in SQL?
2. What is the difference between CROSS Join and INNER Join?
3. How to transpose columns to rows in SQL?
4. How to select first row in each GROUP BY group?