How to filter records from date in MySQL?

Question

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!

VoodooChild · Accepted Answer · 2011-09-05 15:15:22Z

14

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

edited Sep 5, 2011 at 15:15

answered Sep 5, 2011 at 15:09

VoodooChild

9,8049 gold badges70 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VoodooChild Over a year ago

Also checkout this stackoverflow.com/q/508791/247184 - its got some pointers.

ajreal · Accepted Answer · 2012-10-16 14:23:27Z

5

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

edited Oct 16, 2012 at 14:23

answered Sep 5, 2011 at 15:07

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

1 Comment

Cyril N. Over a year ago

%y => Wouldn't it be %d instead ? here you are comparing year and month where day & month is asked.

Collectives™ on Stack Overflow

How to filter records from date in MySQL?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related