How to Generate All Dates Within Multiple Date Ranges in SQL?

Generating Dates Within Multiple Date Ranges

When the requirement extends beyond a single date range, generating all dates within multiple date ranges presents a challenge. This article addresses such scenarios, providing an effective solution.

The Problem

As illustrated in the SQL Fiddle provided, the task is to generate all dates for a given set of multiple date ranges, as follows:

<br>ID      START_DATE      END_DATE<br>101  April, 01 2013  April, 10 2013<br>102   May, 10 2013    May, 12 2013<br>

The Solution

The following query addresses this problem:

select 
  A.ID, 
  A.START_DATE+delta dt
from 
  t_dates A, 
  (
     select level-1 as delta 
     from dual 
     connect by level-1 <= (
       select max(end_date - start_date) from t_dates
     )
  )
where A.START_DATE+delta <= A.end_date
order by 1, 2

Breakdown of the Solution

• The subquery creates a hierarchical structure (using CONNECT BY) to generate a sequence of numbers up to the maximum duration of the date ranges.
• The main query joins this sequence with the input date ranges, adding the sequence numbers to the start dates to generate all possible dates.
• The WHERE clause ensures that only valid dates within each range are generated.
• The ORDER BY clause sorts the output by ID and then by date.

Example Output

For the input provided, the query returns the following output:

<br>ID  Dates<br>101 April, 01 2013<br>101 April, 02 2013<br>101 April, 03 2013<br>101 April, 04 2013<br>101 April, 05 2013<br>101 April, 06 2013<br>101 April, 07 2013<br>101 April, 08 2013<br>101 April, 09 2013<br>101 April, 10 2013<br>102   May, 10 2013<br>102   May, 11 2013<br>102   May, 12 2013<br>

The above is the detailed content of How to Generate All Dates Within Multiple Date Ranges in SQL?. For more information, please follow other related articles on the PHP Chinese website!