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