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

You could use DATE_FORMAT to extract the day and month parts of the birthday dates.

EDIT: sorry i didn't see that he wasn't using MySQL.

Answer 2

Better, Add the difference in years to the BIRTHDAY date, to make everything this year, and then do your compares

SELECT * FROM Employees WHERE
  DATEADD ( year, YEAR(@Today) - YEAR(@Birthday), birthday) BETWEEN @Today AND @EndDate

Answer 3

@p4bl0

He is using Microsoft SQL, not MySQL

Answer 4

You could use the DAYOFYEAR function but be careful when you want to look for January birthdays in December. I think you'll be fine as long as the date range you're looking for doesn't span the New Year.

Answer 5

Assuming this is T-SQL, use DATEPART to compare the month and date separately.

http://msdn.microsoft.com/en-us/library/ms174420.aspx

Alternatively, subtract January 1st of the current year from everyone's birthday, and then compare using the year 1900 (or whatever your epoch year is).

Answer 6

Most of these solutions are close, but you have to remember a few extra scenarios. When working with birthdays and a sliding scale, you must be able to handle the transition into the next month.

For example Stephens example works great for birthdays up until the last 4 days of the month. Then you have a logic fault as the valid dates if today was the 29th would be :29, 30, AND then 1, 2, 3 of the NEXT month, so you have to condition for that as well.

An alternative would be to parse the date from the birthday field, and sub in the current year, then do a standard range comparison.

Answer 7

Another thought: Add their age in whole years to their birthday (or one more if their Birthday hasn't happened yet and then compare as you do above. Use DATEPART and DATEADD to do this.

http://msdn.microsoft.com/en-us/library/ms186819.aspx

The edge case of a range spanning the year would have to have special code.

Bonus tip: consider using BETWEEN...AND instead of repeating the Birthday operand.

Answer 8

Sorry didn't see the requirement to neutralize the year.

select * from Employees
where DATEADD (year, DatePart(year, getdate()) - DatePart(year, Birthday), Birthday)
      between convert(datetime, getdate(), 101) 
              and convert(datetime, DateAdd(day, 5, getdate()), 101)

This should work.

Answer 9

This should work...

DECLARE @endDate DATETIME
DECLARE @today DATETIME

SELECT @endDate = getDate()+6, @today = getDate()

SELECT * FROM Employees 
    WHERE 
    (DATEPART (month, birthday) >= DATEPART (month, @today)
        AND DATEPART (day, birthday) >= DATEPART (day, @today))
    AND
    (DATEPART (month, birthday) < DATEPART (month, @endDate)
        AND DATEPART (day, birthday) < DATEPART (day, @endDate))

Answer 10

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 11

I faced the same problem with my college project a few years ago. I responded (in a rather weasel way) by splitting the year and the date(MM:DD) in two separate columns. And before that, my project mate was simply getting all the dates and programatically going through them. We changed that because it was too inefficient - not that my solution was any more elegant either. Also, its probably not possible to do in a database that has been in use for a while by multiple apps.

Answer 12

No real answer, just another bordercase to consider..: People whose birthday is Feb 29th..

Answer 13

Give this a try:

SELECT * FROM Employees
WHERE DATEADD(yyyy, DATEPART(yyyy, @Today)-DATEPART(yyyy, Birthday), Birthday) > @Today 
AND DATEADD(yyyy, DATEPART(yyyy, @Today)-DATEPART(yyyy, Birthday), Birthday) < DATEADD(dd, @NumDays, @Today)

Answer 14

Best use of datediff and dateadd. No rounding, no approximates, no 29th of february bug, nothing but date functions

1. ageOfThePerson = DATEDIFF(yyyy, dateOfBirth, GETDATE())
2. dateOfNextBirthday = DATEADD(yyyy, ageOfThePerson + 1, dateOfBirth)
3. daysBeforeBirthday = DATEDIFF(d, GETDATE(), dateofNextBirthday)

Answer 15

Nuts! A good solution between when I started thinking about this and when I came back to answer. :)

I came up with:

select  (365 + datediff(d,getdate(),cast(cast(datepart(yy,getdate()) as varchar(4)) + '-' + cast(datepart(m,birthdt) as varchar(2)) + '-' + cast(datepart(d,birthdt) as varchar(2)) as datetime))) % 365
from    employees
where   (365 + datediff(d,getdate(),cast(cast(datepart(yy,getdate()) as varchar(4)) + '-' + cast(datepart(m,birthdt) as varchar(2)) + '-' + cast(datepart(d,birthdt) as varchar(2)) as datetime))) % 365 < @NumDays

You don't need to cast getdate() as a datetime, right?

Answer 16

Upcoming Birthday for the Employee - Sqlserver

DECLARE @sam TABLE ( EmployeeIDs int, dob datetime ) INSERT INTO @sam (dob, EmployeeIDs) SELECT DOBirth, EmployeeID FROM Employee

SELECT * FROM ( SELECT *, bd_this_year = DATEADD(YEAR, DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, dob), dob) FROM @sam s ) d WHERE d.bd_this_year > DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND d.bd_this_year <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 3)

Regards,

Samdoss (Software Programmer) [email protected] ECGroup Datasoft Pvt Ltd

Answer 17

I hope this helps u in some way....

select Employeename,DOB from Employeemaster where day(Dob)>day(getdate()) and month(DOB)>=month(getDate())

Answer 18

This is a combination of a couple of the answers that was tested. This will find the next brithday after a certain date and the age they will be. Also the numdays will limit the range you are looking 7 days = week etc.

SELECT DISTINCT FLOOR(DATEDIFF(dd,Birthday, @BeginDate) / 365.25) + 1 age,
DATEADD(yyyy, FLOOR(DATEDIFF(dd,Birthday, @BeginDate) / 365.25) + 1, Birthday) nextbirthday, birthday
FROM         table
WHERE     DATEADD(yyyy, FLOOR(DATEDIFF(dd,Birthday, @BeginDate) / 365.25) + 1, Birthday) > @BeginDate  
AND DATEADD(yyyy, FLOOR(DATEDIFF(dd,Birthday, @BeginDate) / 365.25) + 1, Birthday) < DATEADD(dd, @NumDays, @BeginDate)
order by nextbirthday

Answer 19

The best way to achieve the same is

DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME

SELECT Member.* from vwMember AS Member
WHERE (DATEADD(YEAR, ((DATEPART(YEAR, @StartDate)) - (DATEPART(YEAR, Member.dBirthDay))), Member.dBirthDay) BETWEEN @StartDate AND @EndDate)