mysql birthday reminder, leap year
- by moodh
Hi, I'm trying to sort out a result set that gives the 5 closest users sorted by upcoming birthday. This works perfectly until leap years comes into play. For example:
May 15th - 96 days left
May 15th - 97 days left
The top result is a birth at 1987 and the lower is from 1988.
u_birth is stored as yyyy-mm-dd.
Is there a simple way to sort this problem without having to rewrite the entire query?
SELECT u_birth, IF( DAYOFYEAR( u_birth ) >= DAYOFYEAR( NOW() ),
DAYOFYEAR( u_birth ) - DAYOFYEAR( NOW() ),
DAYOFYEAR( u_birth ) - DAYOFYEAR( NOW() ) +
DAYOFYEAR( CONCAT( YEAR( NOW() ), '-12-31' ) )
)
AS distance
FROM (blog_users)
WHERE `s_agehide` = 0
ORDER BY distance ASC
LIMIT 5
This query is taken and modified from the mysql manual: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#c7489
