declare @emp table
(empid int identity(1,1) primary key clustered, dob datetime not null)
insert into @emp (dob)
SELECT convert(datetime,'1962-12-11 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1958-03-28 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 = dateadd(yy,datediff(yy,dob,getdate()),dob)
from
@emp a
where
--Birthday between tomorrow and 7 days from today
datediff(dd,getdate(),dateadd(yy,datediff(yy,dob,getdate()),dob))
between 1 and 7
Results:
empid dob BirthdayThisYear
----------- ----------------------- -----------------------
6 1990-09-04 00:00:00.000 2010-09-04 00:00:00.000
7 1985-09-05 00:00:00.000 2010-09-05 00:00:00.000
8 1985-09-10 00:00:00.000 2010-09-10 00:00:00.000