CODEWITHSUNDEEP
sqlsql-server-2005
Wes Price
Wes Price

Reputation: 346

SQL Server 2005, Calculating upcoming birthdays from date of birth

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

Upvotes: 5

Views: 2962

Answers (5)

kiran
kiran

Reputation: 1342

Please Try This one.

SELECT TOP 10 BirthDate, FirstName
FROM Customers
WHERE DATEPART(mm,BirthDate) >= DATEPART(mm,GETDATE())
AND DATEPART(day,BirthDate) >= DATEPART(day,getdate())
OR DATEPART(mm,BirthDate) > DATEPART(mm,getdate())
ORDER BY DatePart(mm,BirthDate),DatePart(day,BirthDate)

this query will get upcoming birthdays including today itself

Upvotes: 1

Asuku
Asuku

Reputation: 19

YEAR(GETDATE() - dbo.Patients.Dob) - 1900

I can safely assume you will never have customers born before 1900

Upvotes: 1

Carvellis
Carvellis

Reputation: 4042

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.

Upvotes: 3

Denis Valeev
Denis Valeev

Reputation: 6015

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

Upvotes: 4

Unreason
Unreason

Reputation: 12704

I think DATEADD should do the proper thing.

Upvotes: 2

Related Questions