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;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes