CODEWITHSUNDEEP
mysqlsql
Jayanga Kaushalya
Jayanga Kaushalya

Reputation: 2744

How to filter records from date in MySQL?

In my database, there is a field called birthday(date) and I want to retrieve records that their birth month and birth day is equal to the current month and the day. Is there a way to write a query for this? Or I just have to do it by retrieving all of the records and find the matching records after by using another program? Thanks!

Upvotes: 7

Views: 6008

Answers (2)

ajreal
ajreal

Reputation: 47331

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

The above query would not make use of mysql index

Upvotes: 5

VoodooChild
VoodooChild

Reputation: 9784

This might also work: (NOTE that I am not sure if you mean day within a Month or day within a week)?

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

Upvotes: 14

Related Questions