php 我如何从mysql中的日期获得月份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2039839/
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 get month from date in mysql
提问by netrox
I want to be able to fetch results from mysql with a statement like this:
我希望能够使用这样的语句从 mysql 获取结果：
SELECT *
FROM table
WHERE amount > 1000
But I want to fetch the result constrained to a certain a month and year (based on input from user)... I was trying like this:
但是我想获取限制在某个月份和年份的结果（基于用户的输入）......我是这样尝试的：
SELECT *
FROM table
WHERE amount > 1000
AND dateStart = MONTH('$m')
...$mbeing a month but it gave error.
...$m是一个月，但它给出了错误。
In that table, it actually have two dates: startDateand endDatebut I am focusing on startDate. The input values would be month and year. How do I phrase the SQL statement that gets the results based on that month of that year?
在该表中，它实际上有两个日期： startDate和endDate，但我注重startDate。输入值将是月份和年份。根据当年的那个月获取结果的 SQL 语句如何表述？
回答by OMG Ponies
You were close - got the comparison backwards (assuming startDateis a DATETIME or TIMESTAMP data type):
你很接近 - 向后进行比较（假设startDate是 DATETIME 或 TIMESTAMP 数据类型）：
SELECT *
FROM table
WHERE amount > 1000
AND MONTH(dateStart) = {$m}
Caveats:
注意事项：
- Mind that you are using mysql_escape_stringor you risk SQL injection attacks.
- Function calls on columns means that an index, if one exists, can not be used
- 请注意，您正在使用mysql_escape_string否则您将面临SQL 注入攻击的风险。
- 对列的函数调用意味着索引（如果存在）不能使用
Alternatives:
备择方案：
Because using functions on columns can't use indexes, a better approach would be to use BETWEENand the STR_TO_DATEfunctions:
因为在列上使用函数不能使用索引，所以更好的方法是使用BETWEEN和STR_TO_DATE函数：
WHERE startdate BETWEEN STR_TO_DATE([start_date], [format])
AND STR_TO_DATE([end_date], [format])
See the documentation for formatting syntax.
有关格式化语法，请参阅文档。
Reference:
参考：
回答by Gattster
Use the month()function.
使用该month()功能。
select month(now());
回答by VolkerK
E.g.
例如
$date = sprintf("'%04d-%02d-01'", $year, $month);
$query = "
SELECT
x,y,dateStart
FROM
tablename
WHERE
AND amount > 1000
AND dateStart >= $date
AND dateStart < $date+Interval 1 month
";
mysql_query($query, ...
This will create a query like e.g.
这将创建一个查询，例如
WHERE
AND amount > 1000
AND dateStart >= '2010-01-01'
AND dateStart < '2010-01-01'+Interval 1 month
+ Interval 1 monthis an alternative to date_add().
+ Interval 1 month是date_add()的替代方法 。
SELECT Date('2010-01-01'+Interval 1 month)-> 2010-02-01SELECT Date('2010-12-01'+Interval 1 month)-> 2011-01-01
This way you always get the first day of the following month. The records you want must have a dateStart before that date but after/equal to the first day of the month (and year) you've passed to sprintf().'2010-01-01'+Interval 1 monthdoesn't change between rows. MySQL will calculate the term only once and can utilize indices for the search.
SELECT Date('2010-01-01'+Interval 1 month)-> 2010-02-01SELECT Date('2010-12-01'+Interval 1 month)->2011-01-01
这样你总能得到下个月的第一天。您想要的记录必须在该日期之前有一个 dateStart，但在您传递给 sprintf() 的月份（和年份）的第一天之后/等于之后/等于。'2010-01-01'+Interval 1 month行之间不会改变。MySQL 将只计算一次术语，并且可以利用索引进行搜索。
回答by Louis
Try this:
尝试这个：
SELECT *
FROM table
WHERE amount > 1000 AND MONTH(dateStart) = MONTH('$m') AND YEAR(dateStart) = YEAR('$m')
回答by Choxmi
Try this
尝试这个
SELECT *
FROM table
WHERE amount > 1000
AND MONTH(datestart)
GROUP BY EXTRACT(YEAR_MONTH FROM datestart)
回答by A.Aleem11
Try this if(date field is text then convert this string to date):
试试这个 if（日期字段是文本然后将此字符串转换为日期）：
SELECT * FROM `table_name` WHERE MONTH(STR_TO_DATE(date,'%d/%m/%Y'))='11'
//This will give month number MONTH(STR_TO_DATE(date,'%d/%m/%Y'))
//If its return 11 then its November
// Change date format with your date string format %d/%m/%Y
回答by Mr.DIpak SOnar
Works in: MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23
适用于：MySQL 5.7、MySQL 5.6、MySQL 5.5、MySQL 5.1、MySQL 5.0、MySQL 4.1、MySQL 4.0、MySQL 3.23
Day:
SELECT EXTRACT(DAY FROM "2017-06-15");Month:
SELECT EXTRACT(MONTH FROM "2017-06-15");Year:
SELECT EXTRACT(YEAR FROM "2017-06-15");
日：
SELECT EXTRACT(DAY FROM "2017-06-15");月：
SELECT EXTRACT(MONTH FROM "2017-06-15");年：
SELECT EXTRACT(YEAR FROM "2017-06-15");
