sql server - SQL query to find employee aniversary -

i need find anniversary date , anniversary year of employees , send email in every 14 days.but have problem last week of december when using following query if start date , end date in different years.

    select * resource     (datepart(dayofyear,joindate)     between datepart(dayofyear,getdate())     , datepart(dayofyear,dateadd(day,14,getdate())))  

instead of comparing dayofyear (which resets 0 @ jan 1st , reason query breaks within 14 days of end of year) update employee's joindate current year purpose of query , compare actual dates

select * resource -- add number of years difference between joindate , current year dateadd(year,datediff(year,joindate,getdate()),joindate)  -- compare range "today" between getdate() -- 14 days today , dateadd(day,14,getdate()) -- duplicate following year or dateadd(year,datediff(year,joindate,getdate())+1,joindate) -- 2016-1-1 between getdate()  , dateadd(day,14,getdate())  

test query:

declare @joindate datetime='2012-1-1' declare @today datetime = '2015-12-26'  select @joindate  dateadd(year,datediff(year,@joindate,@today),@joindate) -- 2015-1-1 between @today -- 2015-12-26 , dateadd(day,14,@today) -- 2016-01-09 or dateadd(year,datediff(year,@joindate,@today)+1,@joindate) -- 2016-1-1 between @today -- 2015-12-26 , dateadd(day,14,@today) -- 2016-01-09 

(h/t @damien_the_unbeliever simple fix)

the above correctly selects joindate in first week of jan (note i've had fudge @today ive not managed invent time travel).

the above solution should solve issue leap years hiding in original solution.

---

update

you expressed in comments requirement select anniversarydate , years of service, need apply case logic determine whether add 1 (year or date) select

select *, case      when dateadd(year,datediff(year,joindate,getdate()),joindate) < getdate()     datediff(year,joindate,getdate())+1     else datediff(year,joindate,getdate()) end [years], case when dateadd(year,datediff(year,joindate,getdate()),joindate) < getdate()     dateadd(year,datediff(year,joindate,getdate())+1,joindate)      else dateadd(year,datediff(year,joindate,getdate()),joindate)  end [anniversarydate]  .... // etc