Get all dates between two dates in SQL Server

Question

How to get the dates between two dates?

I have a variable @MAXDATE which is storing the maximum date from the table. Now I want to get the all dates between @Maxdate and GETDATE() and want to store these date in a cursor.

So far I have done as follows:

;with GetDates As  
(  
select DATEADD(day,1,@maxDate) as TheDate
UNION ALL  
select DATEADD(day,1, TheDate) from GetDates  
where TheDate < GETDATE()  
)  

This is working perfectly but when I am trying to store these values in a cursor

SET @DateCurSor=CURSOR FOR
                SELECT TheDate
                FROM GetDates

Compilation Error

Incorrect syntax near the keyword 'SET'.

How to solve this.

Thanks in advance

Answer 1

You can use this script to find dates between two dates. Reference taken from this Article:

DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME

SET @StartDateTime = '2015-01-01'
SET @EndDateTime = '2015-01-12';

WITH DateRange(DateData) AS 
(
    SELECT @StartDateTime as Date
    UNION ALL
    SELECT DATEADD(d,1,DateData)
    FROM DateRange 
    WHERE DateData < @EndDateTime
)
SELECT DateData
FROM DateRange
OPTION (MAXRECURSION 0)
GO

Answer 2

I listed dates of 2 Weeks later. You can use variable @period OR function datediff(dd, @date_start, @date_end)

declare @period INT, @date_start datetime, @date_end datetime, @i int;

set @period = 14
set @date_start = convert(date,DATEADD(D, -@period, curent_timestamp))
set @date_end = convert(date,current_timestamp)
set @i = 1

create table #datesList(dts datetime)
insert into #datesList values (@date_start)
while @i <= @period
    Begin
        insert into #datesList values (dateadd(d,@i,@date_start))
        set @i = @i + 1
    end
select cast(dts as DATE) from #datesList
Drop Table #datesList

Answer 3

Just saying...here is a more simple approach to this:

declare @sdate date = '2017-06-25'
    , @edate date = '2017-07-24'

; with dates_CTE (date) as (
        select @sdate 
    Union ALL
        select DATEADD(day, 1, date)
        from dates_CTE
        where date < @edate
) select 
    *
from dates_CTE