Tobias Schlemmer's Website

MySQL-Query: Howto select upcoming birthdays

By Tobias December 20, 2007 uncategorized

In a recent software project I needed to select the upcoming birthdays of the
users. But now I detected a problem with the turn of the year. After some time
of researching and trying, I found a stable solution. For your convenience,
here’s the code:
SET @today = '2007-12-20';
SELECT
user_id,
birthday,
DATE_FORMAT(@today, '%Y') - DATE_FORMAT(birthday, '%Y') + IF(
DATE_FORMAT(birthday, '%m%d') < DATE_FORMAT(@today, '%m%d'), 1, 0) AS new_age, DATEDIFF(birthday + INTERVAL YEAR(@today) - YEAR(birthday) + IF(DATE_FORMAT(@today, '%m%d') > DATE_FORMAT(birthday, '%m%d'), 1, 0) YEAR,
@today) AS days_to_birthday
FROM users
HAVING days_to_birthday < 14 ORDER BY days_to_birthday ASC;
And you can easily extend it to limit the result to some specific ages. This is
very useful if you want only the big O coming up:
...
HAVING days_to_birthday < 14 AND new_age IN (10,20,30,40,50,60,70,80,90,100) ...

Tagged on:

16 thoughts on “MySQL-Query: Howto select upcoming birthdays”

  5. Patrick

    This is great, worked like a charm.
    Although I found one little problem if the birthdate is next year.

    Ex. Today is Nov 25 and the birthdate is Jan 4, the new_age is short by 1. I’ll try fixing this on my own and hopefully I remember to post it here. Anyway, thanks!

  6. Patrick

    ~Here’s the fix I was talking about~

    For the line that says:

    DATE_FORMAT(@today, ‘%Y’) – DATE_FORMAT(birthday, ‘%Y’) + IF(
    DATE_FORMAT(birthday, “%m%d”) < DATE_FORMAT(@today, "%m%d"), 1, 0) AS new_age,

    I just replaced 0 with:

    IF(DATE_FORMAT(birthday, "%m%d") = DATE_FORMAT(@today, "%m%d"), 0, 1)

  7. Tobias Post author

    Thanks for your reply. But the statement as posted on the top is correct. Try it again with the following dates, and you should get the same results like me (supposed today is 2011-11-25):

    || *user_id* || *birthday* || *new_age* || *days_to_birthday* ||
    || 1 || 2000-01-04 || 12 || 40 ||
    || 2 || 2000-11-24 || 12 || 365 ||
    || 3 || 2000-11-25 || 11 || 0 ||
    || 4 || 2000-11-26 || 11 || 1 ||

    • #2 (who has had birthday yesterday) has to wait 365 more days. That means the leap year is taken into consideration: 2012-11-25 is 366 days from today.
    • #3 Happy birthday to you: you are getting 11 years old today.
    • #4 can celebrate his 11th birthday tomorrow.
    • #1 has to wait 40 more days until his 12th birthday.
  8. Patrick

    Yes, thanks for that but if you look at your person #1, his birthday is 2000-01-04 and is 12 years old on 2011-11-25.

    After 40 days however, he should be 13 years old (new_age).

    The same is true for your person #2.

    After 365 days, his “new_age” should be 13.

  9. Patrick

    Know what, sorry about the last comment. Obviously I’m not 100% awake.

    You are correct, the “new_age” for both #1 and #2 should be 12.

    I’ll try this out tonight when I get back home from work because I’m a little busy at the moment. I’ll let you know what I find out.

    Thanks!

  10. Patrick

    I see now why we are having different results.

    Your query has instead. Yours is definitely better and more efficient.

    Sorry for making a scene.

    Thanks for your help though! :)

  11. Patrick

    Your query for the new_age has “>” (greater than) instead. Yours is definitely better and more efficient.

  13. Vladimir

    Thank you a lot, you helped me very much. But I had to change the query replacing HAVING with WHERE.

  16. Jawaad

    I have search from many sources, but no perfect solution for upcoming birthday!
    And now, here, this is what I’m looking for!
    This query will show exactly upcoming birthday, ignoring scope of month and year! This mean even the current day is in last month or last year, upcoming birthday will still there for the next month/year as espected!

    Thanks!