MySQL 按 DATEPART() 选择行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13402012/
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 row by DATEPART()
提问by user1827257
I need to get rows from the database where the records are of one month. I tried this SELECT:
我需要从数据库中获取记录为一个月的行。我试过这个选择：
$result = mysql_query("SELECT * FROM my_table WHERE DATEPART('month', date_column)=11");
In database is a lot of rows that have a date in the 11. month, but i do not get any results. Can anyone help me? Thank!
在数据库中有很多行的日期在 11. 月，但我没有得到任何结果。谁能帮我？谢谢！
回答by MvG
There is no DATEPARTfunction in MySQL. Use MONTH(date_column)or EXTRACT(MONTH FROM date_column)instead.
DATEPARTMySQL 中没有函数。使用MONTH(date_column)或EXTRACT(MONTH FROM date_column)代替。
回答by Imre L
SELECT * FROM my_table WHERE MONTH(date_column)=11
回答by Luis Siquot
If you have an index on date_column and you concern about performance it is better to NOT apply functions over the column. (Be aware that this solution, do not anwsers exactly what you asked, becouse it also involves the year)
如果您在 date_column 上有一个索引并且您担心性能，最好不要在该列上应用函数。（请注意，此解决方案，不要完全按照您的要求回答，因为它也涉及年份）
-- For mysql specific:
SELECT * FROM my_table
WHERE date_column >= '2012-11-01'
and date_column < '2012-11-01' + INTERVAL 1 MONTH
-- For tsql specific:
SELECT * FROM my_table
WHERE date_column >= '2012-11-01'
and date_column < DATEADD(month,1,'2012-11-01')
回答by Shubham Verma
If your DATEPART is not working in MYSQL then you can use:
如果您的 DATEPART 在 MYSQL 中不起作用，那么您可以使用：
SELECT * FROM MyTable WHERE MONTH(joinningDate)=MONTH(NOW())-1 and YEAR(joinningDate)=YEAR(NOW());
It will return all records who have inserted in MyTable in last month.
它将返回上个月在 MyTable 中插入的所有记录。
