sql - 减1年时如何保持闰年

Question

我有一个查询，它为我提供了过去 15 年中每一年的给定日期。当我的开始日期是 2 月 29 日时，它不会返回 2012、2008 和 2004 年的 29。我如何让这个查询返回这些年份的 29？

DECLARE @TempDate1 TABLE (Entry_Date Date)
INSERT INTO @TempDate1 values ('2016-02-29')
;WITH
      a AS(SELECT DATEADD(yy,-1,Entry_Date) d,  DATEADD(yy,-1,Entry_Date) d2,0  i
        FROM @TempDate1 
          UNION all
        SELECT DATEADD(yy,-1,d),DATEADD(yy,-1,d2),i+1 FROM a WHERE i<14),
      b AS(SELECT d,d2, DATEDIFF(dd,0,d)%7 dd,i FROM a)
 SELECT 
 d AS Entry_Date
 FROM b

它返回这个:

Entry_Date
2015-02-28
2014-02-28
2013-02-28
2012-02-28
2011-02-28
2010-02-28
2009-02-28
2008-02-28
2007-02-28
2006-02-28
2005-02-28
2004-02-28
2003-02-28
2002-02-28
2001-02-28

虽然我想要这个:

Entry_Date
2015-02-28
2014-02-28
2013-02-28
2012-02-29
2011-02-28
2010-02-28
2009-02-28
2008-02-29
2007-02-28
2006-02-28
2005-02-28
2004-02-29
2003-02-28
2002-02-28
2001-02-28

Answer 1

也许 DateAdd 与临时计数表一致

示例

Declare @YourTable Table ([Entry_Date] date)
Insert Into @YourTable Values 
 ('2016-02-29')
,('2015-07-22')

Select YearNr = N
      ,Anniv  = dateadd(YEAR,N*-1,Entry_Date) 
 From  @YourTable A
 Cross Apply (
                Select Top 15 N=Row_Number() Over (Order By (Select NULL)) From master..spt_values n1
             ) B

返回