Display employee anniversary dates within the next month or year of current date

Question

basically trying to create a query that will display employee anniversary dates for upcoming month or year of current date, also would like to display a column that shows the years of service

SELECT
Employee,
Hire_Date

CASE WHEN DATEADD(YY,DATEDIFF(yy,Hire_Date,GETDATE()),Hire_Date)<GETDATE() THEN DATEDIFF(yy,Hire_Date,GETDATE())
ELSE DATEDIFF(yy,Hire_Date,GETDATE())-1 END AS 'Years of service'

FROM
MyTable

looking to display employees with anniversary dates coming up in the coming month or in the next year

Answer 1

Here is the script validated (see pciture below) to display the employees with birth_date coming in the next month

Replace mytable by your own table

declare @mytable as table(employe varchar(100), birth_date datetime,hire_date datetime)

insert into @mytable values
('name1','01/01/1972','01/01/2000') ,
('name2','12/02/1985','01/02/2003') ,
('name3','04/12/1990','03/04/2005') ,
('name4','05/03/1969','12/12/2005') ,
('name5','04/02/1968','12/02/2010') ,
('name6','04/04/1968','12/11/2009') ,
('name7','12/03/1978','01/01/2019') ,
('name8','01/12/2000','03/02/2018') ,
('name9','12/12/1970','05/02/2019') ,
('name10','04/04/1980','04/04/2018') 

select employe,birth_date,hire_date,
CASE WHEN DATEADD(YY,DATEDIFF(yy,Hire_Date,GETDATE()),Hire_Date)<GETDATE() THEN DATEDIFF(yy,Hire_Date,GETDATE())
ELSE DATEDIFF(yy,Hire_Date,GETDATE())-1 END AS 'Years of service'
from @mytable where (
month(getdate()) < 12 
and 
month(birth_date)=1+month(getdate()) )
or (month(getdate())=12 and month(birth_date)=1)