Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Order by birthday, disregarding year

How do you make a query where you ORDER BY birthday disregarding the year altogether. I need to eliminate/disregard the year and ORDER BY birthdate month and birthdate day from today's date in either ASC or DESC.

The below won't work because the years of the birthdate come into play. The below example shows what happens when the year is regarded:

John   01/02/1974
Billy  11/15/2000
Ally   06/25/2008

SELECT * FROM users ORDER BY birthdate

Expected results when ordering by birthday:

John   01/02/1974
Ally   06/25/2008
Billy  11/15/2000
like image 493
Patriotec Avatar asked Jun 26 '12 17:06

Patriotec


2 Answers

EDIT: @AaronBertrand's comment is correct, day-of-year doesn't hold for leap years. You could use his solution. Another way is to order by month and day, like:

SELECT * FROM users ORDER BY month(birthdate), day(birthdate)
like image 53
Andomar Avatar answered Oct 31 '22 09:10

Andomar


This will normalize all dates to the year 2000:

ORDER BY DATEADD(YEAR, 2000-YEAR(birthday), birthday);

This will handle leap year babies correctly.

like image 21
Aaron Bertrand Avatar answered Oct 31 '22 08:10

Aaron Bertrand