如何在MySQL日期中提取月和年并进行比较?

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.

我发现这个月()，但它只有一个月。我找月找年。

6 个解决方案

#1

in Mysql Doku: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_extract

在Mysql中多库: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';

#2

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';

#3

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,'%Y%m')

#4

SELECT * FROM Table_name Where Month(date)='10' && YEAR(date)='2016';

#5

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?

就像有一个月()函数一样，有一年()函数。如果你在做比较，有没有理由把日期缩短?你真的有兴趣忽略基于一天的差异吗?如果有的话，这就是你想做的吗?

#6

There should also be a 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.)

至于比较，你可以比较年份和月份的第一天，或者你可以把年/月对转换成一个适合比较的数字(例如:bigger = later)。(留给读者的练习。有关提示，请参阅ISO日期格式。)

Or you could use multiple comparisons (i.e. years first, then months).

或者你也可以使用多个比较(例如，先数年，然后数个月)。

#1