MYSQL: Get Upcoming Birthdays Year End Issue-CodePudding

Currently, I have a MySQL query to return upcoming birthdays from the current date to the next 7 days, and it is working fine, but since it's the end of the year I have issues getting results.

The main issue is after December 25th it checks birthdays between 12-26 and 01-02 due to the end of the year. Below is my code


SELECT u.id, u.birthday FROM users as u
WHERE DATE_FORMAT(u.birthday, '%m-%d') >= DATE_FORMAT(NOW(), '%m-%d') and DATE_FORMAT(u.birthday, '%m-%d') <= DATE_FORMAT((NOW()   INTERVAL  7 DAY), '%m-%d');

Can someone help me to update this query to get results?

I tried to add the year-to-date format but it doesn't give expected results.

CodePudding user response：

You need to check if the year in a week is different from the current year, and use a different condition. The condition then should be OR rather than AND.

WHERE CASE WHEN YEAR(NOW()) = YEAR(NOW()   INTERVAL 7 DAY)
        THEN DATE_FORMAT(u.birthday, '%m-%d') BETWEEN DATE_FORMAT(NOW(), '%m-%d') AND DATE_FORMAT((NOW()   INTERVAL  7 DAY), '%m-%d')
        ELSE DATE_FORMAT(u.birthday, '%m-%d') >= DATE_FORMAT(NOW(), '%m-%d') OR DATE_FORMAT(u.birthday, '%m-%d') <= DATE_FORMAT((NOW()   INTERVAL  7 DAY), '%m-%d')
    END