This does a compare against birthday of this year, OR next year, being between date range of today thru next 7 days.

I threw in a sample case of someone being born on 2/29 - I found it interesting how when adding years to be the current year, this rolls back to 2/28! I guess this makes since - 3/1 will always be AFTER their birthday, and 7 days prior to that would be either 2/22 (on leap years), or 2/21 (on other years)

DECLARE @dob TABLE (empid int, empname varchar(50), dob datetime);

INSERT into @dob

SELECT 1, 'test1', '19700105' UNION ALL -- this should be selected for dates 12/29-01/05

SELECT 2, 'test2', '19800825' UNION ALL -- this should be selected for dates 8/18 - 8/25

SELECT 3, 'test3', '19820907' UNION ALL -- this should be selected for dates 8/31 - 9/7

SELECT 4, 'test4', '19790910' UNION ALL -- this should be selected for dates 9/3 - 9/10

SELECT 5, 'test5', '19720903' UNION ALL -- this should be selected for dates 8/27 - 9/3

SELECT 6, 'test6', '19850911' UNION ALL -- this should be selected for dates 9/4 - 9/11

SELECT 7, 'test7', '19740102' UNION ALL -- this should be selected for dates 12/26 - 1/2

SELECT 8, 'test8', '19800229' -- born on leap day! should be select for dates 2/22 - 2/29 on leap years, and 2/21 - 2/28 for non-leap years

DECLARE @start datetime,

@end datetime,

@base datetime;

SELECT @base = '20100220', -- test year rollover by changing to '20101231'

@start = DateAdd(day, DateDiff(day, 0, @base), 0),

@end = DateAdd(day, 7, @start);

WITH CTE AS

(

SELECT *,

BirthDay = DateAdd(year, DateDiff(year, dob, @base), dob)

FROM @dob

)

SELECT empid, empname, dob

FROM CTE

WHERE BirthDay between @start and @end

-- to handle when birthday is in beginning of the year,

-- need to add a year to the birthday to check.

OR DateAdd(year, 1, BirthDay) between @start and @end;