Here's a fix to Michael's:

declare @emp table

(empid int identity(1,1) primary key clustered, dob datetime not null)

insert into @emp (dob)

SELECT convert(datetime,'1962-01-01 00:00:00.000') AS dob UNION ALL

SELECT convert(datetime,'1958-12-31 00:00:00.000') AS dob UNION ALL

SELECT convert(datetime,'1960-02-29 00:00:00.000') AS dob UNION ALL

SELECT convert(datetime,'1990-08-30 00:00:00.000') AS dob UNION ALL

SELECT convert(datetime,'1990-09-03 00:00:00.000') AS dob UNION ALL

SELECT convert(datetime,'1990-09-04 00:00:00.000') AS dob UNION ALL

SELECT convert(datetime,'1985-09-05 00:00:00.000') AS dob UNION ALL

SELECT convert(datetime,'1985-09-10 00:00:00.000') AS dob UNION ALL

SELECT convert(datetime,'1985-09-11 00:00:00.000')

select

a.*,

BirthdayThisYear = CASE

WHEN dateadd(yy,datediff(yy,dob,getdate()),dob) < getdate()

THEN dateadd(yy,datediff(yy,dob,getdate())+1,dob)

ELSE dateadd(yy,datediff(yy,dob,getdate()),dob)

END

from

@emp a

where

--Birthday between tomorrow and 7 days from today

datediff(dd,getdate() ,(CASE

WHEN dateadd(yy,datediff(yy,dob,getdate()),dob) < getdate()

THEN dateadd(yy,datediff(yy,dob,getdate())+1,dob)

ELSE dateadd(yy,datediff(yy,dob,getdate()),dob)

END))

between 1 and 7

Still looks a bit ugly.

All this does is always compare against the NEXT birthday (never a previous one the same year).