SQL Select Upcoming Birthdays

Question

I'm trying to write a stored procedure to select employees who have birthdays that are upcoming.

SELECT * FROM Employees WHERE Birthday > @Today AND Birthday < @Today + @NumDays

This will not work because the birth year is part of Birthday, so if my birthday was '09-18-1983' that will not fall between '09-18-2008' and '09-25-2008'.

Is there a way to ignore the year portion of date fields and just compare month/days?

This will be run every monday morning to alert managers of birthdays upcoming, so it possibly will span new years.

Here is the working solution that I ended up creating, thanks Kogus.

SELECT * FROM Employees  WHERE Cast(DATEDIFF(dd, birthdt, getDate()) / 365.25 as int)     - Cast(DATEDIFF(dd, birthdt, futureDate) / 365.25 as int)  <> 0 

Answer 1

Note: I've edited this to fix what I believe was a significant bug. The currently posted version works for me.

This should work after you modify the field and table names to correspond to your database.

SELECT    BRTHDATE AS BIRTHDAY  ,FLOOR(DATEDIFF(dd,EMP.BRTHDATE,GETDATE()) / 365.25) AS AGE_NOW  ,FLOOR(DATEDIFF(dd,EMP.BRTHDATE,GETDATE()+7) / 365.25) AS AGE_ONE_WEEK_FROM_NOW FROM    "Database name".dbo.EMPLOYEES EMP WHERE 1 = (FLOOR(DATEDIFF(dd,EMP.BRTHDATE,GETDATE()+7) / 365.25))           -           (FLOOR(DATEDIFF(dd,EMP.BRTHDATE,GETDATE()) / 365.25)) 

Basically, it gets the # of days from their birthday to now, and divides that by 365 (to avoid rounding issues that come up when you convert directly to years).

Then it gets the # of days from their birthday to a week from now, and divides that by 365 to get their age a week from now.

If their birthday is within a week, then the difference between those two values will be 1. So it returns all of those records.

Answer 2

In case someone is still looking for a solution in MySQL (slightly different commands), here's the query:

SELECT  name,birthday,  FLOOR(DATEDIFF(DATE(NOW()),birthday) / 365.25) AS age_now,  FLOOR(DATEDIFF(DATE_ADD(DATE(NOW()),INTERVAL 30 DAY),birthday) / 365.25) AS age_future  FROM user  WHERE 1 = (FLOOR(DATEDIFF(DATE_ADD(DATE(NOW()),INTERVAL 30 DAY),birthday) / 365.25)) - (FLOOR(DATEDIFF(DATE(NOW()),birthday) / 365.25))  ORDER BY MONTH(birthday),DAY(birthday)