如何创建递归查询来获取两个日期之间的所有日期

我想获取两个日期之间的所有日期arrival date and leaving date.

我尝试了递归查询，但没有成功。

;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)

您需要递归 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

根据您的样本：

;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