如何在 MySQL 日期中提取月份和年份并进行比较？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7830987/
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
How do I extract Month and Year in a MySQL date and compare them?
提问by JohnSmith
How do I extract the month and date from a mySQL date and compare it to another date?
如何从 mySQL 日期中提取月份和日期并将其与另一个日期进行比较？
I found this MONTH() but it only gets the month. I looking for month and year.
我找到了这个 MONTH() 但它只得到了月份。我正在寻找月份和年份。
回答by betasux
in Mysql Doku: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_extract
在 Mysql Doku 中：http: //dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_extract
SELECT EXTRACT( YEAR_MONTH FROM `date` )
FROM `Table` WHERE Condition = 'Condition';
回答by Question Overflow
If you are comparing between dates, extract the full date for comparison. If you are comparing the years and months only, use
如果您在日期之间进行比较，请提取完整日期进行比较。如果您只比较年份和月份，请使用
SELECT YEAR(date) AS 'year', MONTH(date) AS 'month'
FROM Table Where Condition = 'Condition';
回答by u976108
While it was discussed in the comments, there isn't an answer containing it yet, so it can be easy to miss. DATE_FORMAT works really well and is flexible to handle many different patterns.
虽然在评论中讨论了它，但还没有包含它的答案，所以很容易错过。DATE_FORMAT 工作得非常好，并且可以灵活地处理许多不同的模式。
DATE_FORMAT(date,'%Y%m')
To put it in a query:
把它放在查询中：
SELECT DATE_FORMAT(test_date,'%Y%m') AS date FROM test_table;
回答by Waseem Ahmad
SELECT * FROM Table_name Where Month(date)='10' && YEAR(date)='2016';
回答by Carth
You may want to check out the mySQL docs in regard to the date functions. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
您可能想查看有关日期函数的 mySQL 文档。http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
There is a YEAR() function just as there is a MONTH() function. If you're doing a comparison though is there a reason to chop up the date? Are you truly interested in ignoring day based differences and if so is this how you want to do it?
有一个 YEAR() 函数，就像有一个 MONTH() 函数一样。如果您正在进行比较，是否有理由缩短日期？您是否真的对忽略基于日的差异感兴趣，如果是这样，这就是您想要的方式吗？
回答by aib
There should also be a YEAR().
还应该有一个 YEAR()。
As for comparing, you could compare dates that are the first days of those years and months, or you could convert the year/month pair into a number suitable for comparison (i.e. bigger = later). (Exercise left to the reader. For hints, read about the ISO date format.)
至于比较，您可以比较那些年和月的第一天的日期，或者您可以将年/月对转换为适合比较的数字（即更大 = 稍后）。（练习留给读者。有关提示，请阅读 ISO 日期格式。）
Or you could use multiple comparisons (i.e. years first, then months).
或者您可以使用多重比较（即先是几年，然后是几个月）。
