获取本月的所有日期和上个月的sql

Question

我想从今天获得当前月份和上个月的所有日期。如果今天是24/02/2017，我想获得日期，例如24/02/2017,23/02/2017,22/02/2107--到23-01-2017在SQL Server中。

Answer 1

使用Recursive CTE

with cte as 
    (
    select getdate() as n 
    union all 
    select dateadd(DAY,-1,n) from cte where dateadd(dd,-1,n)> DATEADD(month, -1, getdate()) 
) 
    select * from cte 

Answer 2

简单WHILE会做的伎俩

declare 
@today date = getdate() 
,@day date 

set @day = @today 

while @day >= DATEADD(month, -1, @today) 
begin 

select @day 
set @day = DATEADD(day, -1, @day) 

end 

如果你想在一个表只是一个临时表中插入这种方式。此外代码纠正迭代，直到今天 -1 天 -1 月像你的例子。

declare 
@today date = getdate() 
,@day date 

declare 
@daysTable table ([day] date not null) 

set @day = @today 

while @day >= DATEADD(DAY, -1, DATEADD(month, -1, @today)) 
begin 

--select @day 
insert into @daysTable values (@day) 

set @day = DATEADD(day, -1, @day) 

end 

select * from @daysTable