仅将日期和月份与 mysql 中的日期字段进行比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24510108/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Compare only day and month with date field in mysql
提问by John
How to compare only day and month with date field in mysql?
For example, I've a date in one table: 2014-07-10
如何仅将日期和月份与 mysql 中的日期字段进行比较？例如，我在一张表中有一个日期：2014-07-10
Similarly, another date 2000-07-10in another table.
I want to compare only day and month is equal on date field.
同样，2000-07-10另一个表中的另一个日期。我只想比较日期字段上的日期和月份是否相等。
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"
回答by Marcus Adams
Use DATE_FORMAT instead:
改用 DATE_FORMAT：
SELECT DATE_FORMAT('2000-07-10','%m-%d')
yields
产量
07-10
Here's your query re-written with DATE_FORMAT():
这是您用DATE_FORMAT()以下内容重写的查询：
SELECT *
FROM table
WHERE DATE_FORMAT(field, '%m-%d') = DATE_FORMAT('2000-07-10', '%m-%d')
AND id = "1"
回答by chresse
you can do it with the DAYOFMONTHand MONTHfunction:
你可以用DAYOFMONTHandMONTH函数来做到这一点：
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:
有关更多信息，请查看手册：
回答by Saharsh Shah
回答by Mihai Iorga
You could use DATE_FORMAT:
你可以使用DATE_FORMAT：
SELECT * FROM `table`
WHERE DATE_FORMAT(STR_TO_DATE(`field`, '%Y-%m-%d'), '%d-%m') = DATE_FORMAT(STR_TO_DATE('2000-07-10','%Y-%m-%d'), '%d-%m')
also the second WHEREis not valid. Anyway you would want to get all rows not only id=1
第二个WHERE也是无效的。无论如何，您不仅希望获得所有行id=1
