mySQL SELECT upcoming birthdays

To get all birthdays in next 7 days, add the year difference between the date of birth and today to the date of birth and then find if it falls within next seven days.

SELECT * 
FROM  persons 
WHERE  DATE_ADD(birthday, 
                INTERVAL YEAR(CURDATE())-YEAR(birthday)
                         + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0)
                YEAR)  
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

If you want to exclude today's birthdays just change > to >=

SELECT * 
FROM  persons 
WHERE  DATE_ADD(birthday, 
                INTERVAL YEAR(CURDATE())-YEAR(birthday)
                         + IF(DAYOFYEAR(CURDATE()) >= DAYOFYEAR(birthday),1,0)
                YEAR)  
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

-- Same as above query with another way to exclude today's birthdays 
SELECT * 
FROM  persons 
WHERE  DATE_ADD(birthday, 
                INTERVAL YEAR(CURDATE())-YEAR(birthday)
                         + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0)
                YEAR) 
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
     AND DATE_ADD(birthday, INTERVAL YEAR(CURDATE())-YEAR(birthday) YEAR) <> CURDATE();


-- Same as above query with another way to exclude today's birthdays 
SELECT * 
FROM  persons 
WHERE  DATE_ADD(birthday, 
                INTERVAL YEAR(CURDATE())-YEAR(birthday)
                         + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0)
                YEAR) 
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
     AND (MONTH(birthday) <> MONTH(CURDATE()) OR DAY(birthday) <> DAY(CURDATE()));

Here is a DEMO of all queries

I did a lot "researches" and here is my solution, I guess it is very easy to understand!

SELECT *,
(366 + DAYOFYEAR(birth_date) - DAYOFYEAR(NOW())) % 366 as left_days
FROM `profile`
ORDER BY left_days;

This is my solution. It also works if date of birth is January 1st and today's date is December 31.

SELECT `id`, `name`, `dateofbirth`,
    DATE_ADD(
        dateofbirth, 
        INTERVAL IF(DAYOFYEAR(dateofbirth) >= DAYOFYEAR(CURDATE()),
            YEAR(CURDATE())-YEAR(dateofbirth),
            YEAR(CURDATE())-YEAR(dateofbirth)+1
        ) YEAR
    ) AS `next_birthday`
FROM `user` 
WHERE 
    `dateofbirth` IS NOT NULL
HAVING 
    `next_birthday` BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
ORDER BY `next_birthday`
LIMIT 1000;

Its very easy and simple. No need to use any if conditions or anything else you just need to use DATE_FORMAT() function of mysql.

Here is my sql query that is

SELECT id,email ,dob FROM `users` where DATE_FORMAT(dob, '%m-%d') >= DATE_FORMAT(NOW(), '%m-%d') and DATE_FORMAT(dob, '%m-%d') <= DATE_FORMAT((NOW() + INTERVAL +7 DAY), '%m-%d')

---