MySQL date finder from today

Question

I have a user table that has a birthday date:

buddy_auto_id int(11) PK user_id varchar(128) buddy_user_id varchar(128) buddy_name varchar(128) buddy_bday date buddyuser_id varchar(20) active enum('Yes','No') requestsentby int(11) whenrequested timestamp 

I try to find 3 users whose birthdays are falling faster than today's date, and then show the number of days until their birthday is ordered soon.

Is this possible in the SQL query, or do I need to pull it out and let PHP execute the equation?

Thank you very much

Answer 1

This should be possible with SQL. You need to compare the current date with the birthday indicated from this year or next year, depending on whether we passed in the current year. After you set the “next birthday” date, use DATEDIFF to determine the number of days removed from the current date.

 SELECT *, DATEDIFF( # determine date of next birthday by adding age in years to birthday year DATE_ADD(buddy_bday, INTERVAL YEAR(CURDATE())-YEAR(buddy_bday) # add a year if we celebrated birthday already this year +(MONTH(buddy_bday)<MONTH(CURDATE()) OR (MONTH(buddy_bday)=MONTH(CURDATE()) AND DAY(buddy_bday) < DAY(CURDATE()))) YEAR), CURDATE()) AS days_to_next_bday FROM user_table ORDER BY days_to_next_bday LIMIT 3; 

Answer 2

First we need to calculate the next birthday, then sort by this value:

 select *, buddy_bday + interval if( month(buddy_bday) < month(now()) or (month(buddy_bday) = month(now()) and day(buddy_bday) < day(now())), year(now())+1, year(now()) ) - year(buddy_bday) year as next_bday from buddies order by next_bday - date(now()); 

A long if statement reveals whether the buddy already had his birthday this year.

Answer 3

You must use the DAYOFYEAR function. Try this query -

 SELECT buddy_auto_id , buddy_bday FROM table_name WHERE DAYOFYEAR(buddy_bday) - DAYOFYEAR(NOW()) > 0 ORDER BY DAYOFYEAR(buddy_bday) - DAYOFYEAR(NOW()) LIMIT 3; 

So, this query only works for the current year.

---

EDITED request 2:

This works for all dates.

 CREATE TABLE birtdays( buddy_auto_id INT(11) NOT NULL AUTO_INCREMENT, buddy_bday DATE DEFAULT NULL, PRIMARY KEY (buddy_auto_id) ); INSERT INTO birtdays VALUES (1, '2011-10-04'), (2, '2011-03-01'), (3, '2011-11-29'), (4, '2011-11-10'), (5, '2011-12-29'), (6, '2011-11-30'), (7, '2011-12-08'), (8, '2011-09-17'), (9, '2011-12-01'), (10, '2011-12-11'); SELECT buddy_auto_id, buddy_bday FROM birtdays, (SELECT @day_of_year:=DAYOFYEAR(NOW())) t ORDER BY DAYOFYEAR(buddy_bday + INTERVAL YEAR(NOW()) - YEAR(buddy_bday) YEAR) - @day_of_year + IF (DAYOFYEAR(buddy_bday + INTERVAL YEAR(NOW()) - YEAR(buddy_bday) YEAR) - @day_of_year > 0, 0, DAYOFYEAR(STR_TO_DATE(CONCAT(YEAR(NOW()), '-12-31'), '%Y-%m-%d'))) LIMIT 3; +---------------+------------+ | buddy_auto_id | buddy_bday | +---------------+------------+ | 6 | 2011-11-30 | | 7 | 2011-12-08 | | 10 | 2012-02-11 | +---------------+------------+