MySQL 按字段的月份选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2997364/
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
Select by Month of a field
提问by Pankaj
How can I get records from my table using month/year? I have a table like this:
如何使用月/年从我的表中获取记录？我有一张这样的表：
Name - varchar
DueDate -datetime
Status -boll
DueDateis project due date, I want record corresponding to month/year, not full date, I mean record for specific month.
DueDate是项目截止日期，我想要与月/年相对应的记录，而不是完整日期，我的意思是特定月份的记录。
How can I do this in mysql?
我怎样才能在mysql中做到这一点？
回答by Keeper
回答by Todd R
You could use a between statement:
您可以使用 between 语句：
select * from Project
where DueDate between '2010-01-01' and '2010-02-01'
回答by Eda
Do not use this here recommended solution with MONTH()and YEAR(). It prevents MySQL to use index on column DueDateif you have one and it forces MySQL to examine all rows in table.
不要将这里推荐的解决方案与MONTH()和 一起使用YEAR()。DueDate如果您有索引，它会阻止 MySQL 在列上使用索引，并且它会强制 MySQL 检查表中的所有行。
Instead, use BETWEENclause like this:
相反，使用这样的BETWEEN子句：
SELECT * FROM Project
WHERE DueDate BETWEEN '2010-01-01' AND '2010-02-01'
Or another solution with IN clause:
或使用 IN 子句的另一种解决方案：
SELECT * FROM Project
WHERE DueDate IN ('2010-01-01', '2010-01-02', '2010-01-03', ..., '2010-01-31')
This two solutions allows MySQL to use index, so performance will be better.
这两种方案允许MySQL使用索引，所以性能会更好。
回答by RobertPitt
SELECT Name FROM Table Where MONTH(datetime) = 1;
1 = January.
1 = 一月。
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_month
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_month
回答by Gowri
it will select current year's specific month
它将选择当前年份的特定月份
SELECT * FROM Project WHERE MONTH(DueDate) = 1 AND YEAR(DueDate) = YEAR(NOW())
回答by Pham Bien
If you input month format "Y-m" you can use:
如果您输入月份格式“Ym”，您可以使用：
SELECT * FROM Project WHERE DATE_FORMAT(DueDate,"%Y-%m") = '2010-01'
SELECT * FROM Project WHERE DATE_FORMAT(DueDate,"%Y-%m") = '2010-01'
回答by oezi
SELECT * WHERE MONTH(DueDate) = '5' AND YEAR(DueDate) = '1987';
回答by Anukul Limpanasil
SELECT * FROM TABLE WHERE DueDate LIKE '2020-01%';
use in case you can substring your date data.
以防万一您可以对日期数据进行子字符串化。
