3

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

Alnitak
  • 334,560
  • 70
  • 407
  • 495
sanders
  • 10,794
  • 27
  • 85
  • 127
  • ok, thought at first you were trying to claim a bug in the `DAYOFYEAR` function. So, of course, the `DAYOFYEAR` is different in a leap year. You need to explain why that's a problem. – Alnitak Mar 14 '12 at 14:01
  • What exactly are you trying to accomplish with that query? – Mosty Mostacho Mar 14 '12 at 14:03
  • 1
    Define works. Of course DayOfYear is different in a leap year, there's one more day in it. – Tony Hopkinson Mar 14 '12 at 14:04
  • I want to show upcoming birthdays of employers. The point is that in a leap year, the person who has his birthday today is not shown in the website. Because the dayofyear of his birthday has passed already. So how can i make this query leap year proof? – sanders Mar 14 '12 at 14:09
  • You need to do DAYOFYEAR(e.birthdate) where birthdate is current year, not year of birth. – Marcus Adams Mar 14 '12 at 14:10
  • @MarcusAdams: If he does that, he'll get `NULL` for years that don't have a February 29th. – WWW Mar 14 '12 at 14:11
  • @Crontab, apparently, `DAYOFYEAR()` for February 29th on non-leap years equals March 1st (60). – Marcus Adams Mar 14 '12 at 14:25

3 Answers3

10

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.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
1

There are 365 days in a normal year, 366 in a leap year. In a normal year, March 1 would be the 60th day of the year. In a leap year, February 29 would be the 60th day of the year. The MySQL function is consistent.

If you really wanted to make it more complicated than it has to be, you could add a day to your DAYOFYEAR(curdate()) if curdate() is greater than or equal to March 1 AND curdate() isn't in a leap year. But I wouldn't recommend doing that.

WWW
  • 9,734
  • 1
  • 29
  • 33
1

Here is a related solution

How to find the Birthday of FRIENDS Who are celebrating today using PHP and MYSQL celebrating-today-using-php-and-mysq

If I understand correctly, the problem you are having is that if your birthday is March 1, since you're looking for the (nth) 60th day of the year, you are getting the wrong day sometimes.

I believe the query in the above solution addresses the issue

Community
  • 1
  • 1
danbgray
  • 582
  • 3
  • 8