CODEWITHSUNDEEP
sqlsql-servert-sql
JulGreen
JulGreen

Reputation: 79

How to keep the leap year when substracting 1 year

I have this query that gives me a given date for each of the past 15 years. When my starting date is February 29 it does not return the 29 for year 2012, 2008 and 2004. How can I have this query to return the 29 for those years?

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

It returns this:

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

While I would like to have this:

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

Upvotes: 3

Views: 206

Answers (3)

Jason A. Long
Jason A. Long

Reputation: 4442

Rewrite tour query like this... Not only will handle leap years without jumping through hoops, it's orders of magnitude more efficient than what you currently have.

 DECLARE @BaseDate DATE = '2016-02-29';

 SELECT 
    Entry_Date = DATEADD(YEAR, t.n, @BaseDate)
 FROM 
    (VALUES (-1),(-2),(-3),(-4),(-5),
            (-6),(-7),(-8),(-9),(-10),
            (-11),(-12),(-13),(-14),(-15) ) t (n);

Results...

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

EDIT: Same functionality when used with a table of dates (I stole John's table)

DECLARE @YourTable TABLE (id INT, Entry_Date DATE);
INSERT INTO @YourTable VALUES (1, '2016-02-29'), (2, '2015-07-22');

 SELECT 
    yt.id,
    Entry_Date = DATEADD(YEAR, t.n, yt.Entry_Date)
 FROM 
    @YourTable yt
    CROSS APPLY (VALUES (-1),(-2),(-3),(-4),(-5),
                        (-6),(-7),(-8),(-9),(-10),
                        (-11),(-12),(-13),(-14),(-15) ) t (n);
GO

Results...

id          Entry_Date
----------- ----------
1           2015-02-28
1           2014-02-28
1           2013-02-28
1           2012-02-29
1           2011-02-28
1           2010-02-28
1           2009-02-28
1           2008-02-29
1           2007-02-28
1           2006-02-28
1           2005-02-28
1           2004-02-29
1           2003-02-28
1           2002-02-28
1           2001-02-28
2           2014-07-22
2           2013-07-22
2           2012-07-22
2           2011-07-22
2           2010-07-22
2           2009-07-22
2           2008-07-22
2           2007-07-22
2           2006-07-22
2           2005-07-22
2           2004-07-22
2           2003-07-22
2           2002-07-22
2           2001-07-22
2           2000-07-22

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81970

Perhaps DateAdd in concert with an ad-hoc tally table

Example

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

Returns

enter image description here

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

Simply by using EOMONTH function (SQL Server 2012 and above):

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 EOMONTH(d) AS Entry_Date
 FROM b;

Rextester Demo

Upvotes: 0

Related Questions