Mysql Dayofyear in leap year

In the following query the leap year is not taken into account.

 SELECT      e.id,
             e.title,
             e.birthdate
 FROM        employers e
 WHERE       DAYOFYEAR(curdate()) <= DAYOFYEAR(e.birthdate)
 AND         DAYOFYEAR(curdate()) +14 >= DAYOFYEAR(e.birthdate)

So in this query the birthdate of someone who is born in a leap year has got a different dayofyear in a non leap year.

How can i adjust the query to make sure it also works in a leap year?

The mysql version i have is: 5.0.67

Solution

Where NOW() is a non-leap year 2011, the problem arises from anybody born on a leap year after February 29 will have an extra day because you are using DAYOFYEAR against the birth year.

DAYOFYEAR('2004-04-01') // DAYOFYEAR(e.birthdate) Returns 92
DAYOFYEAR('2011-04-01') // DAYOFYEAR(NOW()) Returns 91

Where you do DAYOFYEAR, you need the birthdate from the current year, not the year of birth.

So, instead of:

DAYOFYEAR(e.birthdate)

You can convert it to this year like this:

DAYOFYEAR(DATE_ADD(e.birthdate, INTERVAL (YEAR(NOW()) - YEAR(e.birthdate)) YEAR))

Which converts a birthdate of:

'2004-04-01'

To:

'2011-04-01'

So, here's the modified query:

SELECT      e.id,
             e.title,
             e.birthdate
 FROM        employers e
 WHERE       DAYOFYEAR(curdate()) <= DAYOFYEAR(DATE_ADD(e.birthdate, INTERVAL (YEAR(NOW()) - YEAR(e.birthday)) YEAR))
 AND         DAYOFYEAR(curdate()) +14 >= DAYOFYEAR(DATE_ADD(e.birthdate, INTERVAL (YEAR(NOW()) - YEAR(e.birthday)) YEAR))

People born on February 29th will fall on March 1st on non-leap years, which is still day 60.