MySQL Birthday Example

Even though I am an Oracle DBA, I enjoy tinkering with MySQL on occasion. I recently needed to write a query that would select birthdays for the next 8 days starting with today’s date. This might seem very straightforward, but what happens if I run the query on December 28th? Only records for Dec 28, 29, 30 and 31 will be returned. Hmmm… I scoured Google and lurked several sites looking for the solution. Believe it or not I couldn’t find any copy-and-paste examples. After tinkering for a while I came up with something. You will first need to create a test table and load it with test data.

Table Creation

CREATE TABLE `birthdays` (
`name` varchar(200) NOT NULL,
`birthday` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Table Data

INSERT INTO `birthdays` VALUES (‘BDAY 01′,’2008-12-01′),(‘BDAY 02′,’2008-12-02′),(‘BDAY 03′,’2008-12-03′),(‘BDAY 04′,’2008-12-04′),(‘BDAY 05′,’2008-12-05′),(‘BDAY 06′,’2008-12-06′),(‘BDAY 07′,’2008-12-07′),(‘BDAY 08′,’2008-12-08′),(‘BDAY 09′,’2008-12-09′),(‘BDAY 10′,’2008-12-10′),(‘BDAY 11′,’2008-12-11′),(‘BDAY 12′,’2008-12-12′),(‘BDAY 13′,’2008-12-13′),(‘BDAY 14′,’2008-12-14′),(‘BDAY 15′,’2008-12-15′),(‘BDAY 16′,’2008-12-16′),(‘BDAY 17′,’2008-12-17′),(‘BDAY 18′,’2008-12-18′),(‘BDAY 19′,’2008-12-19′),(‘BDAY 20′,’2008-12-20′),(‘BDAY 21′,’2008-12-21′),(‘BDAY 22′,’2008-12-22′),(‘BDAY 23′,’2008-12-23′),(‘BDAY 24′,’2008-12-24′),(‘BDAY 25′,’2008-12-25′),(‘BDAY 26′,’2008-12-26′),(‘BDAY 27′,’2008-12-27′),(‘BDAY 28′,’2008-12-28′),(‘BDAY 29′,’2008-12-29′),(‘BDAY 30′,’2008-12-30′),(‘BDAY 31′,’2008-12-31′),(‘BDAY 32′,’2008-01-01′),(‘BDAY 33′,’2008-01-02′),(‘BDAY 34′,’2008-01-03′),(‘BDAY 35′,’2008-01-04′),(‘BDAY 36′,’2008-01-05′),(‘BDAY 37′,’2008-01-06′),(‘BDAY 38′,’2008-01-07′),(‘BDAY 39′,’2008-01-08′),(‘BDAY 40′,’2008-01-09′),(‘BDAY 41′,’2008-01-10′),(‘BDAY 42′,’2008-01-11′),(‘BDAY 43′,’2008-01-12′),(‘BDAY 44′,’2008-01-13′),(‘BDAY 45′,’2008-01-14′),(‘BDAY 46′,’2008-01-15′),(‘BDAY 47′,’2008-01-16′),(‘BDAY 48′,’2008-01-17′),(‘BDAY 49′,’2008-01-18′),(‘BDAY 50′,’2008-01-19′),(‘BDAY 51′,’2008-01-20′),(‘BDAY 52′,’2008-01-21′),(‘BDAY 53′,’2008-01-22′),(‘BDAY 54′,’2008-01-23′),(‘BDAY 55′,’2008-01-24′),(‘BDAY 56′,’2008-01-25′),(‘BDAY 57′,’2008-01-26′),(‘BDAY 58′,’2008-01-27′),(‘BDAY 59′,’2008-01-28′),(‘BDAY 60′,’2008-01-29′),(‘BDAY 61′,’2008-01-30′),(‘BDAY 62′,’2008-01-31′);

The Query

select
name,
concat(date_format(birthday, ‘%m’),’-', date_format(birthday, ‘%d’))
from
birthdays
where
((date_format(birthday, ‘%m%d’) between date_format(curdate(), ‘%m%d’) and if((month(curdate()) = 12 and day(curdate()) > 24), date_format(str_to_date(’12312008′, ‘%m%d%y’), ‘%m%d’), date_format(adddate(curdate(), interval 7 day), ‘%m%d’)))
or
(date_format(birthday, ‘%m%d’) between if((month(curdate()) = 12 and day(curdate()) > 24), date_format(str_to_date(’01012008′, ‘%m%d%y’), ‘%m%d’), date_format(curdate(), ‘%m%d’)) and if((month(curdate()) = 12 and day(curdate()) > 24), date_format(adddate(str_to_date(’01012008′, ‘%m%d%y’), interval (6-(31-day(curdate()))) day), ‘%m%d’), date_format(adddate(curdate(), interval 7 day), ‘%m%d’))));

query in a text file: Birthday Example Query

In order to test this, I had to (Do so at your own risk!) adjust the system date on the server that MySQL is running on to 12-20 then 12-21 then 12-22 then 12-23…. etc until I rolled over to January. After each date adjustment, I would run the query to ensure the results were what I expected. This is the easiest way to demo the script. For some people it may be impossible to change the system time, for others it may be extremely dangerous. Please do this at your own risk.

The where clause is looking for records that are between the current day and current day + 8 days. This works for most of the year, except that once you get to the end of December, you will not get records for January until the next year begins. If this scenario occurs it is detected in the where clause and the necessary records from January are returned also. By doing this the script always returns the current day + 8 no matter what month it is.

Advertisement