We can find upcoming birthdays by simply using between clause in normal days, but problem occurs when you reach the end of year. Given script will definitely help to solve this issue.
Using the code
Create Temporary Calendar to store upcoming dates and join with Master Table on day and month.
The following script is quite lengthy, but works in all scenarios. We can use between clause to find out upcoming days, but query fails when current date reach to 29th Dec
and we want to find birthday's coming in next 3 days. Given query will definitely show the birthday coming on 1st Jan in this scenario.
declare @TempEmp table (EmpName varchar(50), DOB datetime)
insert into @TempEmp
select 'Sahana','1990-11-06' union
select 'Princy','1989-08-25' union
select 'Sameer','1984-07-06' union
select 'Yogesh','1987-01-01' union
declare @TempCal table (dates datetime)
declare @CurrDate datetime
declare @InNextDays int
declare @BaseDate datetime
set @CurrDate= getdate() set @InNextDays=3
declare @cnt int
insert into @TempCal
select dateadd(day, @cnt, @CurrDate)
select a.EmpName, a.DOB
from @TempEmp a
inner join @TempCal b on datepart(day, a.DOB)=datepart(day, b.Dates) and
datepart(month, a.DOB)=datepart(month, b.Dates)
order by b.dates