SQL Server : join date range in select

Question

I've selected some data like

Select startDate(01.09.19), endDate(30.09.19) 
from DataTable

And I have to transform this select like if I receive one row with start date 01.12.2019 and end date 31.12.2019, I should make 31 rows: each of row contains the next date in this date range.

Row defined above should transform into

StartDate:01.12.2019, EndDate:31.12.2019, Day:01.12.2019;
StartDate:01.12.2019, EndDate:31.12.2019, Day:02.12.2019;
... 
StartDate:01.12.2019, EndDate:31.12.2019, Day:31.12.2019;

How could I perform this in one select?

Answer 1

Just another option if you don't have a Calendar Table or Numbers Table, you can use an ad-hoc tally table

Example

Declare @YourTable table (startDate date,endDate date)
Insert Into @YourTable values
('2019-01-01','2019-12-31')


Select A.*
      ,NewValue = DateAdd(DAY,N-1,startDate)
 From  @YourTable A
 Cross Apply (  Select Top (datediff(day,startDate,endDate)+1) N=Row_Number() Over (Order By (Select NULL)) From master..spt_values n1, master..spt_values n2 ) B

Returns

startDate   endDate     NewValue
2019-01-01  2019-12-31  2019-01-01
2019-01-01  2019-12-31  2019-01-02
2019-01-01  2019-12-31  2019-01-03
2019-01-01  2019-12-31  2019-01-04
...
2019-01-01  2019-12-31  2019-12-31