How to filter records from a date in MySQL?

Question

My database has a happy birthday field (date), and I want to get records saying that their month of birth and birthday are equal to the current month and day. Is there a way to write a query for this? Or do I just need to do this by extracting all the records and finding the corresponding records after using another program? Thank!

Answer 1

This may also work: (NOTE that I'm not sure if you mean a day for a month or a day for a week)?

SELECT * FROM TABLE
WHERE MONTH(birthday) = MONTH(CURRENT_DATE)
AND DAY(birthday) = DAY(CURRENT_DATE)    --assuming day within a month

Answer 2

select * from table
where date_format(birthday, '%m%d')=date_format(current_date, '%m%d');

Mysql index will not be used in the above query