Saturday, October 10, 2009

MySQL Query to check if a birthday is upcoming in 7 days

Someone probably has done this before, but in case it hasn't been done, here's my query:

$result = mysql_query("SELECT FirstName, LastName, DATE_FORMAT(birthday, '%M %d') as bday, CONCAT(IF(MONTH(birthday)=1 AND MONTH(CURRENT_DATE())=12, YEAR(CURRENT_DATE())+1, YEAR(CURRENT_DATE())), DATE_FORMAT(birthday, '-%m-%d')) AS fakebirthday FROM birthdays WHERE CONCAT(IF(MONTH(birthday)=1 AND MONTH(CURRENT_DATE())=12, YEAR(CURRENT_DATE())+1, YEAR(CURRENT_DATE())), DATE_FORMAT(birthday, '-%m-%d')) BETWEEN CURRENT_DATE() AND DATE_ADD(CURRENT_DATE(), INTERVAL 7 DAY) ORDER BY fakebirthday;");

What it does (this is PHP code, but the stuff in double-quotes is the real meat.):

  1. birthday is stored in datetime format
  2. The SELECT AS is relatively straight-forward. I don't want to divulge age/years of this list, so I'm only reporting month and day (January 1) in the result
  3. I'm making a fakebirthday calculation in order to sort/Order by. Unfortunately, I can't use the fakebirthday in "WHERE" clause, so I repeated the calculation to do my "WHERE".
  4. The idea is to check for the next 7 days, and cheat by prepending *this* year on the birthday's month and day. Unfortunately, if this month is December (12) and the birthday is in January (1), this won't work, so add 1 to this year and prepend it to the January birthdates -- but only when running this in December.
  5. of course, check if it's between today and 7 days from today
  6. and sort by the "fake" birthday because it's easier to sort this way so that January comes after December


Oh, I know it's ugly code. It's probably slow, too. However, the idea is this should run once a day (once a week?) on fairly small amounts of data (office active employee records).

1 comment:

midhun k. murali said...

Very nice.... Good code.

Blog Archive