get all date in current month and last month in sql

Question

I would like to get all dates in a current month and last month from today.like, if today is 24/02/2017 and i want to get dates like 24/02/2017,23/02/2017,22/02/2107-- to 23-01-2017 in SQL Server.

Answer 1

use 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

A simple WHILE will do the trick

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

If you want it in a table just insert in a temp table this way. Also code corrected to iterate until today -1 day -1 month like in your example.

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