如何获得今天过去 12 个月的日期? [英] How do I get the last 12 months date of today?

问题描述

如何获取截至今天的过去 12 个月的日期?

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

例如:

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

我试过了:

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

但这只是上个月给我的.

But this gives me only last month.

推荐答案

如果你想生成那些行，你可以使用递归查询:

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

这将为您提供今天的日期以及前 12 个月的同一天(因此总共 13 行).您可以将 cte 中的不等式条件调整为所需的确切迭代次数.如果需要100次以上的迭代，则需要在查询的末尾添加option (maxrecursion 0).

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.

这篇关于如何获得今天过去 12 个月的日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！