Create a list with dates which are in between start and end dates

Question

Using SQL Server 2016. I have a table with different start and end dates

Start            | End
-----------------+-----------------
2018-01-01 00:00 | 2018-01-01 23:59  
2018-01-12 05:33 | 2018-01-13 13:31  
2018-01-24 22:00 | 2018-01-27 01:44

I now want to create a list (stored in a table variable), that contains the dates (in minute steps from start to end) for each record. There can be multiple records in the original table.

Result:

2018-01-01 00:00 (1st row StartDate)   
2018-01-01 00:01  
2018-01-01 00:02  
2018-01-01 00:03  
... until  
2018-01-01 23:59 (1st row EndDate)  
followed by  
2018-01-12 05:33 (2nd row StartDate)   
2018-01-12 05:34  
2018-01-12 05:35  
2018-01-12 05:36  
... until  
2018-01-13 13:31 (2nd row EndDate)  
and so on.

Answer 1

In ANSI standard SQL and many databases, you can use a recursive CTE:

with recursive cte as (
      select datestart as dte, datestart, dateend
      from t
      union all
      select dte + interval '1 minute', datestart, dateend
      from cte
      where dte < dateend
     )
select dte
from cte
order by datestart, dateend, dte;

Note that this is ANSI syntax. The recursive keyword may not be allowed by some databases. Date arithmetic varies by database. Some databases may limit the number of rows being generates, so defaults need to be overridden.

EDIT:

In SQL Server, this would be:

with cte as (
      select datestart as dte, datestart, dateend
      from t
      union all
      select dateadd(minut, 1, dte), datestart, dateend
      from cte
      where dte < dateend
     )
select dte
from cte
order by datestart, dateend, dte
option (maxrecursion 0);