SQL Select Upcoming Birthdays

Tag: , ,

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

26 Answers

  1. arora on Mar 05, 2013

    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.

  2. joseph on Mar 05, 2013

    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)

  3. xequnsruuh on Mar 05, 2013

    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)

    Thanks to @Gustavo Cardoso, new definition for the age of the person

    1. ageOfThePerson = FLOOR(DATEDIFF(d,dateOfBirth, GETDATE())/365.25)

  4. cheeseballlumpy82 on Mar 05, 2013

    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.

  5. pharme86 on Mar 05, 2013

    I found the solution for this. This may save someone’s precious time.

     select EmployeeID,DOB,dates.date  from emp_tb_eob_employeepersonal 
 cross join dbo.GetDays(Getdate(),Getdate()+7) as dates where weekofmonthnumber>0
 and month(dates.date)=month(DOB) and day(dates.date)=day(DOB)



GO
/****** Object:  UserDefinedFunction [dbo].[GetDays]    Script Date: 11/30/2011 13:19:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--SELECT [dbo].[GetDays] ('02/01/2011','02/28/2011')

ALTER FUNCTION [dbo].[GetDays](@startDate datetime, @endDate datetime)
RETURNS @retValue TABLE
(Days int ,Date datetime, WeekOfMonthNumber int, WeekOfMonthDescription varchar(10), DayName varchar(10))
AS
BEGIN
    DECLARE @nextDay int
    DECLARE @nextDate datetime 
    DECLARE @WeekOfMonthNum int 
    DECLARE @WeekOfMonthDes varchar(10) 
    DECLARE @DayName varchar(10) 
    SELECT @nextDate = @startDate, @WeekOfMonthNum = DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH,0,@startDate),0),@startDate) + 1, 
    @WeekOfMonthDes = CASE @WeekOfMonthNum 
        WHEN '1' THEN 'First' 
        WHEN '2' THEN 'Second' 
        WHEN '3' THEN 'Third' 
        WHEN '4' THEN 'Fourth' 
        WHEN '5' THEN 'Fifth' 
        WHEN '6' THEN 'Sixth' 
        END, 
    @DayName 
    = DATENAME(weekday, @startDate)
SET @nextDay=1
WHILE @nextDate <= @endDate 
BEGIN 
    INSERT INTO @retValue values (@nextDay,@nextDate, @WeekOfMonthNum, @WeekOfMonthDes, @DayName) 
    SELECT @nextDay=@nextDay + 1 
SELECT @nextDate = DATEADD(day,1,@nextDate), 
    @WeekOfMonthNum 
    = DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH,0, @nextDate),0), @nextDate) + 1, 
    @WeekOfMonthDes 
    = CASE @WeekOfMonthNum 
    WHEN '1' THEN 'First' 
    WHEN '2' THEN 'Second' 
    WHEN '3' THEN 'Third' 
    WHEN '4' THEN 'Fourth' 
    WHEN '5' THEN 'Fifth' 
    WHEN '6' THEN 'Sixth' 
    END, 
    @DayName 
    = DATENAME(weekday, @nextDate) 
    CONTINUE 
END 

WHILE(@nextDay <=31)
BEGIN


    INSERT INTO @retValue values (@nextDay,@nextDate, 0, '', '') 
    SELECT @nextDay=@nextDay + 1

END

    RETURN
END

    Make a cross join with the dates and check for the comparison of month and dates.

  6. johan-offer on Mar 05, 2013

    This is solution for MS SQL Server:
    It returns employees with birthdays in 30 days.

    SELECT * FROM rojstni_dnevi
  WHERE (DATEDIFF   (dd, 
                    getdate(), 
                    DATEADD (   yyyy, 
                                DATEDIFF(yyyy, rDan, getdate()),
                                rDan)
    nex             )
        +365) % 365 < 30

  7. xequnsruuh on Mar 05, 2013

    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.

  8. cheeseballlumpy82 on Mar 05, 2013

    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.

  9. johan-offer on Mar 05, 2013

    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).

  10. harry on Mar 05, 2013

    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.

  11. johan-offer on Mar 05, 2013

    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.

  12. rajesh on Mar 05, 2013

    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))

  13. arora on Mar 05, 2013

    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.

  14. joseph on Mar 05, 2013

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

  15. harry on Mar 05, 2013

    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)

  16. arora on Mar 05, 2013

    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?

  17. xequnsruuh on Mar 05, 2013

    I hope this helps u in some way….

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

  18. johan-offer on Mar 05, 2013

    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

  19. pharme86 on Mar 05, 2013

    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)

  20. fernando-correia on Mar 05, 2013

    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)

  21. cheeseballlumpy82 on Mar 05, 2013

    I used this for MySQL, probably not the most efficient way to query but simple enough to implement. 

    select * from `schema`.`table` where date_format(birthday,'%m%d') >= date_format(now(),'%m%d') and date_format(birthday,'%m%d') < date_format(DATE_ADD(NOW(), INTERVAL 5 DAY),'%m%d');

  22. scott on Mar 05, 2013

    i believe this ticket has been closed ages ago but for the benefit of getting the correct sql query please have a look.

    SELECT Employee_Name, DATE_OF_BIRTH
FROM Hr_table 
WHERE 

/**
fetching the original birth_date and replacing the birth year to the current but have to  deduct 7 days to adjust jan 1-7 birthdate.
**/

datediff(d,getdate(),DATEADD(year,datediff(year,DATEADD(d,-7,hr.DATE_OF_BIRTH),getdate()),hr.date_of_birth)) between 0 and 7

-- current date looks ahead to 7 days for upcoming modified year birth date.

order by

-- sort by no of days before the birthday
datediff(d,getdate(),DATEADD(year,datediff(year,DATEADD(d,-7,hr.DATE_OF_BIRTH),getdate()),hr.date_of_birth))

  23. fernando-correia on Mar 05, 2013

    Liked the approach of @strelc, but his sql was a bit off. Here’s an updated version that works well and is simple to use:

    SELECT * FROM User 
WHERE (DATEDIFF(dd, getdate(), DATEADD(yyyy, DATEDIFF(yyyy, birthdate, getdate()), birthdate)) + 365) % 365 <= <number of days>

  24. bhtpjogetu on Mar 05, 2013

    @p4bl0

    He is using Microsoft SQL, not MySQL

  25. rajesh on Mar 05, 2013

    Try my solution… I have Informix database… 

    SELECT person, year(today)-year(birthdate) as years, birthdate,

CASE
WHEN MOD(year(birthdate)+((year(today)-year(birthdate))+1),4)<>0 AND MONTH(birthdate)=2 AND DAY(birthdate)=29 THEN 
   CASE 
   WHEN mdy(month(birthdate), 28, year(birthdate)+((year(today)-year(birthdate))+1))-today >= 365 THEN (mdy(month(birthdate), 28, year(birthdate)+((year(today)-year(birthdate))+1))-today)-365
   WHEN mdy(month(birthdate), 28, year(birthdate)+((year(today)-year(birthdate))+1))-today < 365 THEN mdy(month(birthdate), 28, year(birthdate)+((year(today)-year(birthdate))+1))-today
   END
ELSE
   CASE 
   WHEN mdy(month(birthdate), day(birthdate), year(birthdate)+((year(today)-year(birthdate))+1))-today >= 365 THEN (mdy(month(birthdate), day(birthdate), year(birthdate)+((year(today)-year(birthdate))+1))-today)-365
   WHEN mdy(month(birthdate), day(birthdate), year(birthdate)+((year(today)-year(birthdate))+1))-today < 365 THEN mdy(month(birthdate), day(birthdate), year(birthdate)+((year(today)-year(birthdate))+1))-today
   END
END until

FROM table_name
WHERE mdy(month(birthdate), day(birthdate), 2000) >= mdy(month(today), day(today), 2000)
AND mdy(month(birthdate), day(birthdate), 2000) <= mdy(month(today), day(today), 2000)+30
OR
mdy(month(birthdate), day(birthdate), 2000) <= mdy(month(today), day(today), 2000)-(365-30)
ORDER BY 4, YEAR(birthdate)

  26. cheeseballlumpy82 on Mar 05, 2013

    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