我如何从MySQL中获取日期的月份 [英] how do I get month from date in mysql

问题描述

我希望能够使用以下语句从mysql中获取结果:

I want to be able to fetch results from mysql with a statement like this:

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

... $m是一个月，但给出了错误.

...$m being a month but it gave error.

在该表中，它实际上有两个日期:startDate和endDate，但我关注的是startDate.输入值将是月份和年份.我该如何措辞根据当年那个月获得结果的SQL语句?

In that table, it actually have two dates: startDate and endDate but 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是DATETIME或TIMESTAMP数据类型):

You were close - got the comparison backwards (assuming startDate is a DATETIME or TIMESTAMP data type):

SELECT * 
  FROM table 
 WHERE amount > 1000 
   AND MONTH(dateStart) = {$m}

注意事项:

---

• 请注意，您正在使用 mysql_escape_string 或您可能会冒险 SQL注入攻击.
• 对列的函数调用意味着如果存在索引，则无法使用

Caveats:

---

• Mind that you are using mysql_escape_string or you risk SQL injection attacks.
• Function calls on columns means that an index, if one exists, can not be used

因为在列上使用函数不能使用索引，所以更好的方法是使用BETWEEN和STR_TO_DATE函数:

Because using functions on columns can't use indexes, a better approach would be to use BETWEEN and the STR_TO_DATE functions:

WHERE startdate BETWEEN STR_TO_DATE([start_date], [format]) 
                    AND STR_TO_DATE([end_date], [format])

有关格式化语法，请参阅文档.

See the documentation for formatting syntax.

• MONTH
• 年份
• BETWEEN
• STR_TO_DATE
• MONTH
• YEAR
• BETWEEN
• STR_TO_DATE

这篇关于我如何从MySQL中获取日期的月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！