Need to get dates between two date columns

Question

I have two date columns, effdate and enddate and need to get a list of dates between two dates in third column.

for example,

effdate      | enddate
2012-09-01   |2019-06-11
2012-10-02   |2012-08-31

I want to get list dates between these two dates as a third column

Can you put an example of the table which you want to get the data from please? If is the case just do it: `select * from [your table] where [date column] between effdate and enddate` — Flavio Francisco, Jun 11 '19 at 12:34

score 0 · Accepted Answer · answered Jun 11 '19 at 12:27

A recursive CTE is a pretty easy method:

with dates as (
      select effdate as dte, enddate
      from t
      union all
      select dateadd(day, 1, dte), enddate
      from dates
      where dte < enddate
     )
select dte
from dates
option (maxrecursion 0);

Often a tally table has somewhat better performance:

with digits as (
      select v.n
      from (values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(n)
     ),
     n as (
      select (d1*1000 + d2*100 + d3*10 + d4*1) n
      from digits d1 cross join
           digits d2 cross join
           digits d3 cross join
           digits d4 
     )
select dateadd(day, n.n, t.effdate)
from t join
     n
     on dateadd(day, n.n, t.effdate) <= t.enddate  ;

You have to be sure that your table of numbers -- however generated -- is big enough for your timespan.

score 0 · Answer 2 · answered Jun 11 '19 at 12:47

You can try this for get your desired list of dates-

DECLARE @D1 DATETIME = '20190601'
DECLARE @D2 DATETIME = '20190611'
DECLARE @LoopCount INT = (SELECT DATEDIFF(DD,@D1,@D2))

DECLARE @TempTable TABLE
(
    date date
)

WHILE @LoopCount >= 0
BEGIN
    INSERT INTO @TempTable (date)
    VALUES (DATEADD(DD,-@LoopCount,GETDATE()))

    SET @LoopCount = @LoopCount - 1
END

SELECT * FROM @TempTable

Need to get dates between two date columns

2 Answers2

Linked