I was having trouble with this particular query from quite some time. I have fixed this problem with some patch up. But the solution was not permanent. The problem is keep coming in the one or the other form. Then one of my colleagues suggested a solution for the problem. I was really happy to get that solution. It was a small change in the way i was putting the logic. I was using date part function of and then find the day and then month and then compare both values (day & month) of start and end date with DOB of the employee. That logic was getting complex and confusing too. My colleague suggested me to make use of the dayofyear option in the date part. dayofyear will get us the unique number for the day in whole year. Just like 1st Jan 2008 or 1 Jan 2009 it will be return us as 1 it doesn't depend on which year it's falling.
Here is the SQL query to find birthday of all the contacts between two date
SELECT COALESCE(C.FirstName,'')
FROM Tb_Customer C WITH (NOLOCK)
WHERE dob IS NOT NULL AND datepart(dayofyear,dob) BETWEEN datepart(dayofyear,getdate()) AND datepart(dayofyear,dateadd(m,3,getdate()))
