How to get all dates between current month and the two last months

  • A+
Category:Languages

I am trying to get all dates existing between the current month and the two last months.

For example: today 10-01-2019 With an sql script, I will get all dates between 2018-10-01 and 2019-01-31.

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

I get

error Msg 530, Level 16, State 1, Line 1 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

 


with cte as   (   select dateadd(month,1,dateadd(day, -1* day(getdate()) , cast(getdate() as date) )   ) n   union all   select  dateadd(day,-1,n) from cte where month(n)  + year(n) * 12 >= month(getdate())  + year(getdate()) * 12 -3   ),  final as (select * from cte except  select top 1 * from cte order by n) select * from final order by n OPTION (MAXRECURSION 1000) 

or to use dateadd only and avoid the except

with cte as   (   select dateadd(day,-1,dateadd(month,1,dateadd(day, 1 - day(getdate()) , cast(getdate() as date)))) n   union all   select  dateadd(day,-1,n) from cte where n > dateadd(month,-3,dateadd(day , 1 - day(getdate()),cast(getdate() as date)))    ) select * from cte order by n OPTION (MAXRECURSION 1000) 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: