MySQL-Query: Howto select upcoming birthdays

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

Tags:

This entry was posted on Thursday, December 20th, 2007 at 8:14 PM. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

12 Responses to “MySQL-Query: Howto select upcoming birthdays”

  • Daniel Sturm Says:
    May 7th, 2009 at 12:26 PM

    Hey, thanx for your post! Saved my day ;)

  • Mathias Schreiber Says:
    November 25th, 2009 at 5:50 PM

    Lifesaver!

  • jamal Says:
    December 6th, 2010 at 12:50 PM

    hey, you rock. I used your code and it’s working fantastic.

  • Mario Says:
    February 22nd, 2011 at 6:56 PM

    Another saved day! thank you!

  • Patrick Says:
    November 25th, 2011 at 6:06 PM

    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!

  • Patrick Says:
    November 25th, 2011 at 6:37 PM

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

  • Tobias Says:
    November 25th, 2011 at 9:04 PM

    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.
  • Patrick Says:
    November 29th, 2011 at 6:07 PM

    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.

  • Patrick Says:
    November 29th, 2011 at 7:22 PM

    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!

  • Patrick Says:
    November 30th, 2011 at 1:21 AM

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

  • Patrick Says:
    November 30th, 2011 at 1:23 AM

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

  • smronju Says:
    February 1st, 2012 at 10:54 AM

    Thanks you saved my day :)

Leave a Reply