Compare only day and month with date field in mysql

How to compare only day and month with date field in mysql? For example, I've a date in one table: 2014-07-10

Similarly, another date 2000-07-10 in another table. I want to compare only day and month is equal on date field.

I tried this format. But I can't get the answer

select *
from table
where STR_TO_DATE(field,'%m-%d') = STR_TO_DATE('2000-07-10','%m-%d')
and id = "1"

Solution 1:

Use DATE_FORMAT instead:

SELECT DATE_FORMAT('2000-07-10','%m-%d')

yields

07-10

Here's your query re-written with DATE_FORMAT():

SELECT *
FROM table
WHERE DATE_FORMAT(field, '%m-%d') = DATE_FORMAT('2000-07-10', '%m-%d')
AND id = "1"

Solution 2:

you can do it with the DAYOFMONTH and MONTH function:

SELECT *
FROM table
WHERE DAYOFMONTH(field) = 31 AND MONTH(field) = 12 AND id = 1;

EDIT: Of course you can write following too if you want to compare two fields:

SELECT *
FROM table
WHERE 
    DAYOFMONTH(field) = DAYOFMONTH(field2) 
    AND MONTH(field) = MONTH(field2) 
    AND id = 1 ...;

for further information have a look at the manual:

1. MONTH
2. DAYOFMONTH

Solution 3:

Use MONTH and DAY functions:

Try this:

SELECT *
FROM tableA a 
WHERE a.id = 1 AND MONTH(a.field) = MONTH('2000-07-10') AND 
      DAY(a.field) = DAY('2000-07-10')