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';
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
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) ...
- data retention
- My 1st marathon – Bonn (DE)
Hey, thanx for your post! Saved my day ;)
hey, you rock. I used your code and it’s working fantastic.
Another saved day! thank you!
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!
~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)
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 ||
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.
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.
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! :)
Your query for the new_age has “>” (greater than) instead. Yours is definitely better and more efficient.
Thanks you saved my day :)
Thank you a lot, you helped me very much. But I had to change the query replacing HAVING with WHERE.
Thanks for that snippet. Saved my day too :-)
great show. Used it in my project. Gave You credits in the source code. Will put Your name on my website, too.
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!