带日期循环的SQL

Question

我一直在尝试编写一个查询，该查询将在36个月内返回每个月的倒数第二天，该查询应自动返回36个月的记录。

例如；例如12月为20201230，1月为20210130，2月为20210228(不包括2月(。然后，我将以这种方式更新表中列中指定的日期。

它应该就像一个循环，或者我可以用光标来做吗？顺便说一句，日期应该是参数化的。感谢您提前帮助

Answer 1

更好的方法是使用tally函数、dateadd和eomonth。

数字功能

create function [dbo].[fnNumbers](
@zero_or_one   bit, 
@n             bigint)
returns table with schemabinding as return
with n(n) as (select null from (values (1),(2),(3),(4)) n(n))
select 0 n where @zero_or_one = 0
union all
select top(@n) row_number() over(order by (select null)) n
from n na, n nb, n nc, n nd, n ne, n nf, n ng, n nh,
n ni, n nj, n nk, n nl, n nm, n np, n nq, n nr;

查询

declare
@start_dt         date='2020-12-25';
select dateadd(day, -1, eomonth(@start_dt, fn.n)) penult_dt
from dbo.fnNumbers(0, 35) fn;

Answer 2

这就是您想要的？

DECLARE @Start DATE='2020-12-25'
;WITH CTE
as
(
SELECT 1 Seq, DATEADD(DAY,-DAY(@Start),DATEADD(MONTH,1,@Start)) Dt
UNION ALL
SELECT Seq+1, DATEADD(DAY,-DAY(@Start),DATEADD(MONTH,Seq+1,@Start)) FROM CTE WHERE Seq+1 <37
)
SELECT FORMAT(Dt,'yyyyMMdd') Dates FROM CTE

Answer 3

您想要当前月份以及接下来36个月的倒数第二天。这里有一个使用递归查询的选项：

with cte as (
select dateadd(month, 1, datefromparts(year(getdate()), month(getdate()), 1)) as dt
union all
select dateadd(month, 1, dt) from cte where dt < dateadd(month, 36, getdate())
)
select dt, dateadd(day, -2, dt) as dt_penultimate 
from cte

这个想法是使用递归查询从下个月开始生成一系列开始的月份。然后在外部查询中，我们只将每个日期偏移2天。

如果愿意，可以将getdate()替换为查询参数。

如果必须生成超过100个月的跨度，则需要在查询的最后添加option(maxrecursion 0)。

DB Fiddle上的演示：

dt|dt_penultimate：--------|：-------------2021-01-01|2020-12-302021-02-01|2021-01-302021-03-01|2021-02-272021-04-01|2021-03-30…