Get an interval of dates from a range of dates

Question

I have two dates 21/10/2019 and 26/6/2031, and from these two I need a list of dates with three months interval starting from the first date like this:

22/10/2019 | 21/01/2020
22/01/2020 | 21/04/2020
22/04/2020 | 21/07/2020
22/07/2020 | 21/10/2020 
...
22/01/2031 | 21/04/2031
22/04/2031 | 26/06/2031

I've tried using ROW_NUMBER() and DENSE_RANK() and LAG() to group a complete list of dates between the two dates, but I can't seem to figure it out. I think I might need to partition this somehow, but I can't get it right.

If you don't understand, please let me know. I'm pretty new at this :)

Answer 1

You can use a recursive query:

with cte (dt, end_dt) as (
    select @start_dt, @end_dt
    union all
    select dateadd(month, 3, dt), end_dt from cte where dt < end_dt
)
select dt, 
    case when dateadd(month, 3, dt) < end_dt 
        then dateadd(day, -1, dateadd(month, 3, dt)) 
        else end_dt 
    end as end_dt
from cte
order by dt;

If you need to generate more than 100 quarters, you need to add option (maxrecursion 0) at the very end of the query.

Demo on DB Fiddle