sql - How do I get the last 12 months date of today?

Question

How can I get the last 12 months date as of today ?

Ex:

2020-10-21
2020-09-21
2020-08-21
2020-07-21
2020-06-21
2020-05-21
2020-04-21
2020-03-21
2020-02-21
2020-01-21

I tried this :

SELECT GETDATE() 'Today', 
           DATEADD(mm,-1,GETDATE())

But this gives me only last month.

Answer 1

If you want to generate those rows, you can use a recursive query:

with cte as (
    select 0 n
    union all select n + 1 from cte where n < 12
)
select dateadd(month, -n, convert(date, getdate())) dt from cte order by dt

This gives you today's date, and the same day of the month for the preceding 12 month (so that's a total of 13 rows). You can adjust the inequality condition in cte to the the exact number of iterations that you want. If you need more than 100 iterations, then you need to add option (maxrecursion 0) at the end of the query.