Sort upcoming birthdays based on current date

Question

I have the following table of people and their birthdays:

name        birthday
----------------------
yannis      1979-06-29
natalia     1980-08-19
kostas      1983-10-27    
christos    1979-07-22
kosmas      1978-04-28

and I have no idea how to sort the names on how closer the birthday is to today. So for NOW() = 2011-09-08 the sorted result should be:

kostas      1983-10-27
kosmas      1978-04-28
yannis      1979-06-29
christos    1979-07-22
natalia     1980-08-19

I'm looking for a quick hack, don't really care for performance (pet project - table will hold less than 1000 records), but of course every suggestion will be extremely appreciated.

@ajreal Her birthday is in August, so she'll have to wait the longest for her party :) — yannis, Sep 08 '11 at 06:13
OK so you want *upcoming* birthdays sorted by the one closest to today? — Salman A, Sep 08 '11 at 06:53
I edited my answer after you accepted. Original answer had problems with leap years. — Salman A, Sep 08 '11 at 08:48

Salman A · Accepted Answer · 2017-12-26T20:52:47.250

Here is one way:

Calculate current year - year of birth
Add the resulting number of years to the date of birth
You now have the birthday this year, if this date has passed then add one more year
Sort the results by that date

SELECT
    name,
    birthday,
    birthday + INTERVAL (YEAR(CURRENT_DATE) - YEAR(birthday))     YEAR AS currbirthday,
    birthday + INTERVAL (YEAR(CURRENT_DATE) - YEAR(birthday)) + 1 YEAR AS nextbirthday
FROM birthdays
ORDER BY CASE
    WHEN currbirthday >= CURRENT_DATE THEN currbirthday
    ELSE nextbirthday
END

Notes:

Today's birthdays appears first regardless of current time
February 29 birthday is treated equal to February 28 birthday for common years e.g.
- On Jan/1/2019 both Feb 28 and Feb 29 birthdays (2019) are sorted equal
- On Mar/1/2019 Feb 28 and Feb 29 birthdays (2020) are sorted as expected

SQLFiddle

score 5 · Answer 2 · edited Mar 02 '14 at 16:15

5

SELECT name
     , birthday
FROM TableX
ORDER BY DAYOFYEAR(birthday) < DAYOFYEAR(CURDATE())
       , DAYOFYEAR(birthday)

No, the above may produce error results, due to years with 366 days. This is correct:

SELECT name
     , birthday
FROM
  ( SELECT name
         , birthday
         , MONTH(birthday) AS m
         , DAY(birthday) As d
    FROM TableX
  ) AS tmp
ORDER BY (m,d) < ( MONTH(CURDATE()), DAY(CURDATE()) )
       , m
       , d

If your table grows to more than a few thousands records, it will be real slow. If you want a fast query, add fields with the month and day and have an index on (bmonth,bday) or add them as one field, either Char (08-17 or 0817 for 17-Aug) or Int (817 for 17-Aug) and an index on that field.

edited Mar 02 '14 at 16:15

arnep

5,971
3
35
51

answered Sep 08 '11 at 06:27

ypercubeᵀᴹ

113,259
19
174
235

Is there any example of rejection for the first query? – Hafez Divandari Jun 25 '17 at 00:45
@Hafez What do you mean with "rejection"? – ypercubeᵀᴹ Jun 25 '17 at 06:31

score 4 · Answer 3 · answered Mar 29 '12 at 17:40

4

Seems to be rather fast, no problems with leap years:

SELECT * 
FROM `people` 
ORDER BY CONCAT(SUBSTR(`birthday`,6) < SUBSTR(CURDATE(),6), SUBSTR(`birthday`,6))

Все гениальное -- просто! ;)

answered Mar 29 '12 at 17:40

Yuri P.

187
5

score 2 · Answer 4 · answered Sep 08 '11 at 06:39

Not pretty, but works

SELECT * 
,CASE WHEN BirthdayThisYear>=NOW() THEN BirthdayThisYear ELSE BirthdayThisYear + INTERVAL 1 YEAR END AS NextBirthday
FROM (
    SELECT * 
    ,birthday - INTERVAL YEAR(birthday) YEAR + INTERVAL YEAR(NOW()) YEAR AS BirthdayThisYear
    FROM bd
) AS bdv
ORDER BY NextBirthday

score 0 · Answer 5 · answered Sep 08 '11 at 06:33

0

i would try it like this (but this isn't tested):

SELECT
  name,
  birthday
FROM
  birthdays
ORDER BY
  ABS( DAYOFYEAR(birthday) - (DAYOFYEAR(CURDATE()) ) ASC

EDIT:
changed ordering from DESC to ASC because you want to get the farthest first, not the closest.

answered Sep 08 '11 at 06:33

oezi

51,017
10
98
115

I dont think this works correctly. An user who had birthday yesterday, would show somewhere close to the top, and I dont think this is what we want. – tomazahlin Sep 17 '15 at 10:02

Sort upcoming birthdays based on current date

5 Answers5

Linked

Related