blocks|key|1147800|text|使用month()函数。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1147801|select+month(now());|code-block|syntax|javascript|1147802|entityMap^0|2|7|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Use the <code>month()</code> function. 

<pre><code>select month(now());
</code></pre>

blocks|key|1362907|text|例如。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1362908|$date+=+sprintf("'%2504d-%2502d-01'",+$year,+$month);
$query+=+"
++SELECT
++++x,y,dateStart
++FROM
++++tablename
++WHERE
++++AND+amount+>+1000
++++AND+dateStart+>=+$date
++++AND+dateStart+<+$date%2BInterval+1+month
";
mysql_query($query,+...|code-block|syntax|javascript|1362909|这将创建一个查询，例如|1362910|WHERE
++AND+amount+>+1000
++AND+dateStart+>=+'2010-01-01'
++AND+dateStart+<+'2010-01-01'%2BInterval+1+month|1362911|%2B+Interval+1+month是date_add()的替代方案。|offset|length|style|CODE|1362912|SELECT+Date('2010-01-01'%2BInterval+1+month)->+2010-02-01|1362913|SELECT+Date('2010-12-01'%2BInterval+1+month)->+2011-01-01|1362914|这样，你总是可以得到下个月的第一天。您想要的记录必须在该日期之前，但在/等于您传递给dateStart+()的月份(和年份)的第一天之后。|1362915|'2010-01-01'%2BInterval+1+month不会在不同行之间更改。MySQL将只计算一次该术语，并且可以利用索引进行搜索。|1362916|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add^0|0|0|0|0|0|I|J|A|0|0|0|16|19|A|0|0|16|19|A|0|0|0|T|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|17|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|18|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|19|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|1A|8|@$M|1B|N|1C|O|P]]|9|@$M|1D|N|1E|1|1F]]|A|$]]|$1|Q|3|R|5|6|7|1G|8|@$M|1H|N|1I|O|P]|$M|1J|N|1K|O|P]]|9|@]|A|$]]|$1|S|3|T|5|6|7|1L|8|@$M|1M|N|1N|O|P]|$M|1O|N|1P|O|P]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1R|8|@$M|1S|N|1T|O|P]]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1U|8|@]|9|@]|A|$]]]|Z|$10|$5|11|12|13|A|$14|15]]]]

E.g.

<pre><code>$date = sprintf("'%04d-%02d-01'", $year, $month);
$query = "
 SELECT
 x,y,dateStart
 FROM
 tablename
 WHERE
 AND amount &gt; 1000
 AND dateStart &gt;= $date
 AND dateStart &lt; $date+Interval 1 month
";
mysql_query($query, ...
</code></pre>

This will create a query like e.g.

<pre><code>WHERE
 AND amount &gt; 1000
 AND dateStart &gt;= '2010-01-01'
 AND dateStart &lt; '2010-01-01'+Interval 1 month
</code></pre>

<code>+ Interval 1 month</code> is an alternative to <a href="http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add" rel="nofollow noreferrer">date_add()</a>.

<code>SELECT Date('2010-01-01'+Interval 1 month)</code>-&gt; <code>2010-02-01</code> 
<code>SELECT Date('2010-12-01'+Interval 1 month)</code>-&gt; <code>2011-01-01</code> 
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(). 
<code>'2010-01-01'+Interval 1 month</code> doesn't change between rows. MySQL will calculate the term only once and can utilize indices for the search.

blocks|key|1147881|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1147882|SELECT+*+
FROM+table+
WHERE+amount+>+1000+AND+MONTH(dateStart)+=+MONTH('$m')+AND+YEAR(dateStart)+=+YEAR('$m')|code-block|syntax|javascript|1147883|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this:

<pre><code>SELECT * 
FROM table 
WHERE amount &gt; 1000 AND MONTH(dateStart) = MONTH('$m') AND YEAR(dateStart) = YEAR('$m')
</code></pre>

blocks|key|1147906|text|尝尝这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1147907|SELECT+*+
++++++FROM+table+
+++++WHERE+amount+>+1000+
+++++++AND+MONTH(datestart)
++++GROUP+BY+EXTRACT(YEAR_MONTH+FROM+datestart)|code-block|syntax|javascript|1147908|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this 

<pre><code>SELECT * 
 FROM table 
 WHERE amount &gt; 1000 
 AND MONTH(datestart)
 GROUP BY EXTRACT(YEAR_MONTH FROM datestart)
</code></pre>

blocks|key|1147934|text|如果(日期字段为文本，则将此字符串转换为日期)，请尝试此操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1147935|SELECT+*+FROM+`table_name`+WHERE+MONTH(STR_TO_DATE(date,'%25d/%25m/%25Y'))='11'

//This+will+give+month+number+MONTH(STR_TO_DATE(date,'%25d/%25m/%25Y'))
//If+its+return+11+then+its+November
//+Change+date+format+with+your+date+string+format++%25d/%25m/%25Y|code-block|syntax|javascript|1147936|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this if(date field is text then convert this string to date):

<pre><code>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
</code></pre>

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

<pre><code>SELECT * 
 FROM table 
 WHERE amount &gt; 1000 
</code></pre>

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:

<pre><code>SELECT * 
 FROM table 
 WHERE amount &gt; 1000 
 AND dateStart = MONTH('$m') 
</code></pre>

...<code>$m</code> being a month but it gave error. 

In that table, it actually have two dates: <code>startDate</code> and <code>endDate</code> but I am focusing on <code>startDate</code>. 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?

how do I get month from date in mysql

MySQL

我希望能够使用下面这样的语句从mysql中获取结果：SELECT *   FROM table  WHERE amount > 1000 但是我想获取某个特定月份和年份的结果(基于用户的输入)……我是这样尝试的：SELECT *   FROM table  WHERE amount > 1000    AND date...

问如何在mysql中从日期获取月份
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在mysql中从日期获取月份EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在mysql中从日期获取月份
EN