[SQL] Generating a List of Dates Between Two Given Dates (From & To date) in Oracle SQL

 

When working with databases, there are situations where you need to generate a list of all dates within a specified date range. This can be useful in reporting, scheduling, or filling in missing dates in datasets. In this blog post, we'll explore how to achieve this in Oracle SQL using a recursive Common Table Expression (CTE).

Using Recursive CTE to Generate Dates in Oracle

Oracle allows us to use a recursive Common Table Expression (CTE) to generate a list of dates dynamically. Below is the SQL query that accomplishes this task:

WITH date_series (date_value) AS (
    SELECT TO_DATE('2024-03-01', 'YYYY-MM-DD') FROM DUAL
    UNION ALL
    SELECT date_value + 1 FROM date_series
    WHERE date_value + 1 <= TO_DATE('2024-03-10', 'YYYY-MM-DD')
)
SELECT date_value FROM date_series;

Explanation of the Query

• The WITH date_series (date_value) clause creates a temporary table-like structure with a column named date_value.

• The first SELECT statement initializes the date series with the starting date (2024-03-01).

• The UNION ALL statement recursively adds one day to date_value.

• The WHERE condition ensures that the recursion stops once the date_value reaches the specified end date (2024-03-10).

• The SELECT date_value FROM date_series; retrieves all generated dates from the recursive CTE.

Note: if you don't use date_series (date_value) then you will get the below error:

ORA-32039: recursive WITH clause must have column alias list

Alternative Approach: Using CONNECT BY

Oracle also provides a simpler method using the CONNECT BY clause:

SELECT TO_DATE('2024-03-01', 'YYYY-MM-DD') + LEVEL - 1 AS date_value
FROM DUAL
CONNECT BY LEVEL <= TO_DATE('2024-03-10', 'YYYY-MM-DD') 

- TO_DATE('2024-03-01', 'YYYY-MM-DD')

+ 1;

Why Use CONNECT BY?

• This approach is more efficient and does not require recursion.

• It leverages Oracle's LEVEL pseudo-column to generate a sequence of numbers.

• The formula TO_DATE('2024-03-10', 'YYYY-MM-DD') - TO_DATE('2024-03-01', 'YYYY-MM-DD') + 1 calculates the total number of days.