SQL Server 2005, Calculating upcoming birthdays from date of birth

Question

This one has bugged me for a while now. Recently when revisiting some code I wrote for a customer a few years ago I was wondering if there is a more elegant solution to the problem.

The customer stores all of their clients information including date of birth (date time field)

They run an extract every Monday that retrieves any customer whose birthday will fall within the following week.

I.e. if the extract was run on Monday Jan 1st, Customers whose birthday fell between (and including) Monday Jan 8th -> Sunday Jan 14th would be retrieved.

My solution was to use the Datepart(dy) function and calculate all upcoming birthdays based off the customers date of birth converted to day of year, adding some logic to include for the extract being run at the end of a year. The problem was that using Day of year throws results off by 1 day if the customer was born on a leap year and / or the extract is run on a leap-year after the 29th of Feb, so once again I had to add more logic so the procedure returned the expected results.

This seemed quite over-kill for what should be a simple task For simplicity let’s say the table 'customer' contains 4 fields, first name, last name, dob, and address.

Any suggestions on how to simplify this would really be appreciated

Wes

Answer 1

Why not use DATEPART(wk) on this year's birthday?

SET DATEFIRST 1  -- Set first day of week to monday
SELECT * FROM customer
WHERE DATEPART(wk, DATEADD(yy, DATEPART(yy, GETDATE()) - DATEPART(yy, customer.dob), customer.dob)) = DATEPART(wk, GETDATE()) + 1

It selects all customers who's birthday's weeknumber is one greater than the current weeknumber.

Answer 2

I think DATEADD should do the proper thing.

Answer 3

Would something like this work for you?

select * from Customers c 
where dateadd(year, 1900-year(dob), dob) 
    between dateadd(year, 1900-year(getdate()), getdate())
    and dateadd(year, 1900-year(getdate()), getdate())+7