CODEWITHSUNDEEP
phpmysqlsqldate
Jordy
Jordy

Reputation: 4809

How to display upcoming birthdays?

I know I'm not the first one who is asking how to display upcoming birthdays. But unfortunately all the other answers didn't help me finding out how to do it.

I have a database with the following structure:

Id    -    Birthday
1          9/14/1996
2          8/27/1990
--         --

As you see, in the Birthday field the date of birth is stored. And I would like to show the five upcoming birthdays (including the birthdays today).

But how can I do that? I tried this, but it didn't work:

    $query="SELECT Id 
    FROM participants
    WHERE DATE_ADD(STR_TO_DATE(birthday, %n/%j/%Y), INTERVAL YEAR(CURDATE())-YEAR(STR_TO_DATE(birthday, %n/%j/%Y)) YEAR)";

Thanks for the help!

Upvotes: 4

Views: 2691

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

The key to this question is to order things, not to use where. This prevents a problem at the end of the year.

So, the logic is to put everything who MM-DD birthday is on or after the current date first, followed by the rest of the birthday in date order:

select p.*
from participants
order by (case when date_format(STR_TO_DATE(birthday, %n/%j/%Y), '%m-%d') >= date_format(now(), '%m-%d')
               then 0 else 1
          end),
         date_format(STR_TO_DATE(birthday, %n/%j/%Y), '%m-%d')
limit 5;

EDIT:

If you need to convert the birthdate:

select p.*
from participants
order by (case when date_format(birthdate, '%m-%d') >= date_format(now(), '%m-%d')
               then 0 else 1
          end),
         date_format(birthdate, '%m-%d')
limit 5;

Upvotes: 4

AK47
AK47

Reputation: 3807

select top 5 * 
from myTable 
where 
      month(dob) >= month(now()) 
  and day(dob) >= day(now()) 
order by dob asc

Upvotes: 1

Related Questions