How to create recursive query to get all dates between two dates

Question

I want to get all dates between two dates arrival date and leaving date.

I tried that recursive query but it didn't work.

;with dates as ( 
SELECT GuestID, ArrivalDate as reserveddate 
from dbo.Guest 
union all 
SELECT GuestID, dateadd (day,1,dbo.Guest. ArrivalDate) as reserveddate 
from dbo.Guest 
where dateadd (day,1,dbo.Guest. ArrivalDate) <dbo.Guest.leavingate
) 

SELECT * 
from dates 
option (maxrecursion 0)

Answer 1

Try This

    DECLARE @DateFrom DateTime = DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())-5,0)
    DECLARE @DateTo DateTime = CONVERT(DATE,GETDATE())

    ;WITH Numbers (Number) AS (
         SELECT row_number() OVER (ORDER BY object_id)
         FROM sys.all_objects
    )

    SELECT  dateadd(DAY, number-1, @DateFrom)           
    FROM    Numbers
    WHERE   number <= datediff(DAY, @DateFrom-1, @DateTo-1)

you can change the change the date interval by changing DAY to WEEK, MONTH, YEAR etc. in the select dateadd and the where datediff

Answer 2

You need recursive CTE:

DECLARE  @arrival_date date = '2016-01-01',
         @leaving_date date = '2016-02-01'

;WITH cte AS (
SELECT @arrival_date as date_
UNION ALL
SELECT CAST(DATEADD(day,1,date_) as date)
FROM cte
WHERE date_ < @leaving_date
)

SELECT *
FROM cte
OPTION (MAXRECURSION 0)

Output:

date_
2016-01-01
2016-01-02
2016-01-03
...
2016-01-30
2016-01-31
2016-02-01

EDIT1

Based on your sample:

;WITH cte AS (
SELECT GuestID, CAST(ArrivalDate as date) as date_
FROM Guests
UNION ALL
SELECT c.GuestID, CAST(DATEADD(day,1,date_) as date)
FROM cte c
INNER JOIN Guests g
    ON g.GuestID = c.GuestID
WHERE date_ < g.LeavingDate
)

SELECT *
FROM cte
ORDER BY GuestID, date_
OPTION (MAXRECURSION 0)

EDIT2

;WITH Guests AS (
SELECT  1 as GuestID,
        '2016-01-01' ArrivalDate,
        '2016-01-05' LeavingDate
UNION ALL
SELECT  2 ,
        '2016-06-17',
        '2016-06-20'
), cte AS (
SELECT GuestID, CAST(ArrivalDate as date) as date_
FROM Guests
UNION ALL
SELECT c.GuestID, CAST(DATEADD(day,1,date_) as date)
FROM cte c
INNER JOIN Guests g
    ON g.GuestID = c.GuestID
WHERE date_ < g.LeavingDate
)

SELECT *
FROM cte
ORDER BY GuestID, date_
OPTION (MAXRECURSION 0)

Output:

GuestID date_
1   2016-01-01
1   2016-01-02
1   2016-01-03
1   2016-01-04
1   2016-01-05
2   2016-06-17
2   2016-06-18
2   2016-06-19
2   2016-06-20

Answer 3

My preference for this type of thing is to use a tally table. I keep a view on every system that looks like this.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally
GO

This way I have a tally table at my fingertips at all times. You can read more about tally tables and how they replace loops here. http://www.sqlservercentral.com/articles/T-SQL/62867/

To solve your current problem this becomes fairly simple. First we need a table and some sample data.

create table #Guest
(
    GuestID int identity
    , ArrivalDate date
    , DepartureDate date
)

insert #Guest
select '2016-06-01', '2016-06-15' union all
select '2016-07-01', '2016-07-12'

Here is where the power of the tally table really shows. To produce the output for your challenge is as simple as this code.

SELECT GuestID
    , ArrivalDate 
    , DepartureDate
    , DATEADD(Day, N - 1, ArrivalDate) as EachDate
from #Guest g
join cteTally t on t.N <= DATEDIFF(day, ArrivalDate, DepartureDate) + 1
order by GuestID
    , EachDate

The other big advantage of this type of approach over a recursive cte is that you are using what is known as a triangular join and can cause some performance challenges. Here is some more info on triangular joins. http://www.sqlservercentral.com/articles/T-SQL/61539/