There is an issue with Leap Year and DateAdd function that can appear if you want to compare two dates from different years and using some date value from table. For example if you have calendar table and use DateAdd function, you can get 2 rows wit two date for one source date instead of expected 1 row. See bellow reason and solution.
The problem is, when you use 29 February and 28 February with DateAdd + one year with table column. Both will return you same date, which means results matrix is multiply 2x.
Quick example to show you issue, and how to change your query in this case to get expected results
First use this query, to create CalendarTable by using Sequence table tricky and reduce dates to only years 2015-2018. Leap Year is 2016 there.
/* Create Sequence table */
SELECT TOP 100000 identity (int ,1,1) as Sequence
INTO #SequenceTable
FROM sysobjects , sys.all_columns
/* Use sequence table to create dates table */
SELECT dateadd(dd,Sequence,-1) [Day]
INTO CalenderTable
FROM #SequenceTable
/* Remove sequence table */
DROP TABLE #SequenceTable
/* Remove dates from 1900-01-01 ... to keep only few test dates */
DELETE FROM CalenderTable WHERE [Day] < '2015-01-01'
DELETE FROM CalenderTable WHERE [Day] > '2019-01-01'
When we have Calendar Table now with all days from 2015 to 2018, try run these 3 queries. As you will see, first will return 2 results instead of 1.
SELECT * FROM [CalenderTable]
WHERE DateAdd(yy, 1, [Day]) = '2017-02-28 00:00:00.000'
SELECT * FROM [CalenderTable]
WHERE [Day] = DateAdd(yy, -1, '2017-02-28 00:00:00.000')
SELECT * FROM [CalenderTable]
WHERE [Day] = DateAdd(yy, -1, '2016-02-29 00:00:00.000')
Here is what will you get as results
Day
-----------------------
2016-02-28 00:00:00.000
2016-02-29 00:00:00.000
(2 row(s) affected)
Day
-----------------------
2016-02-28 00:00:00.000
(1 row(s) affected)
Day
-----------------------
2015-02-28 00:00:00.000
(1 row(s) affected)
Reason for first results is adding 1 year to table row. 28 Feb + year is 28 Feb, but 29 Feb on Year Leap is 28 Feb on none Year Leap. Two dates are united to one day, so two rows has same future compare date.
On the oposite side, take away one year from compared date will not create this issue at all. 28 Feb minus one year will be 28 Feb as well even year before is Leap Year. 29 Feb minus one year will be 28 Feb, which has corresponding one table row as well.
Keep this in mind when you are using DateAdd to prevent some column multiply.
