Is it possible to write a SELECT statement to obtain all dates between 2 date columns?

Question

My question is related to these 2 questions:

1. https://stackoverflow.com/questions/17529860/how-to-list-all-dates-between-two-dates/27319120
2. https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function

However in my case, I have a table with an ID,StartDate,EndDate columns and I want to apply the same approach (but instead of 2 days, I want to use the entire table).

And before dropping into functions, I wanted to see if this is possible w/ a select and a date dimension table.

Basically I want to scale this up to the entire table:

SELECT 
    t.Id
    ,dd.[Date]
FROM dbo.DateDimension dd
    ,dbo.T1 t   
WHERE
    t.Id = 957
    and  dd.Date>=t.StartDate and dd.Date <= t.EndDate 

I have Sample Data + Desired Output (The Select Statement) here:
www.sqlfiddle.com/#!18/0e389/2

Answer 1

There is probably something I don't understand in your question, but from the looks of it all you have to do is remove the predicate t.id = ? You can shorten the query a bit by using BETWEEN predicate, I also prefer explicit JOINS over "," joins:

SELECT t.Id, dd.[Date]
FROM dbo.DateDimension dd
JOIN dbo.dbse t   
    ON dd.Date BETWEEN t.StartDate and t.EndDate;

In your example in the fiddle, you output 3 id's. If you only want a subset you can use an IN predicate:

SELECT t.Id, dd.[Date]
FROM dbo.DateDimension dd
JOIN dbo.dbse t   
    ON dd.Date BETWEEN t.StartDate and t.EndDate;
WHERE t.id IN (4229, 4268, 4269);    

Answer 2

I believe you are looking for the following, leveraging the TFV from you second reference:

SELECT 
    t.Id
    Exploded.*

FROM dbse t
  CROSS APPLY ExplodeDates(t.StartDate, t.EndDate) AS Exploded

For each row in t, the TVF will be called and will produce a row for each record in the TVF results.

Answer 3

Without using a table, get date on the fly without using cursor.

DECLARE @SDate DATE = '20140101',
            @EDate DATE = '20140106';
    
    SELECT  TOP (DATEDIFF(DAY, @SDate, @EDate) + 1)
            ResultDate = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
    FROM    sys.all_objects a
            CROSS JOIN sys.all_objects b;