根据起止日期生成连续日期

根据起止日期生成连续的日期，又两种方法，如下：

--- 方法一

select ContractRoomID,dateadd(day,number,'2016-07-15'),0 from ContractRoom cc

left join master.dbo.spt_values on   number <=DATEDIFF(day,'2016-07-15','2016-08-15')

where cc.ContractRoomID='R150006716C001' and type ='P'

结果：

 --- 方法二，这种方法只能在MSSQL 2008 以上版本中执行, 使用了CTE公共表达式中递归的思想

declare  @t  table(id varchar(50) , bizdate date);

declare @startDate date = '2016-06-01'; 

declare @EndDate date =  '2016-08-15' ;

with cte0 as 

(select   'R160016001' as contractRoomId , @startDate as startDate, @EndDate as endDate ), 

cte1 As 
(

    Select  contractRoomId , startDate As targetDate from cte0 

    union all

    Select contractRoomId , dateadd(day,1,targetDate) From cte1 

    Where dateadd(day,1,targetDate)<=@EndDate 
)  

insert into @t (id, bizdate)

select contractRoomId, targetDate From cte1  ;

select  * from @t

结果：

方法2的递归思想，可参考
Actual Practice : [with CTE]&[convert]&[coalesce] as in my work - 4