blocks|key|3238087|text|这是用于将日期转换为字符串的SQL+Server+syntax。在MySQL中，您可以使用DATE函数从datetime中提取日期：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3238088|SELECT+*
FROM+players
WHERE+DATE(us_reg_date)+BETWEEN+'2000-07-05'+AND+'2011-11-10'|code-block|syntax|javascript|3238089|但是，如果您想要利用列us_reg_date上的索引，则可能需要尝试执行以下操作：|style|CODE|3238090|SELECT+*
FROM+players
WHERE+us_reg_date+>=+'2000-07-05'
++AND+us_reg_date+<+'2011-11-10'+%2B+interval+1+day|3238091|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms187928.aspx|1|http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date^0|E|H|0|19|4|1|0|0|B|B|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]|$A|12|B|13|1|14]]|C|$]]|$1|D|3|E|5|F|7|15|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|16|8|@$A|17|B|18|K|L]]|9|@]|C|$]]|$1|M|3|N|5|F|7|19|8|@]|9|@]|C|$G|H]]|$1|O|3|-4|5|6|7|1A|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]|W|$5|R|S|T|C|$U|X]]]]

That is <a href="http://msdn.microsoft.com/en-us/library/ms187928.aspx" rel="noreferrer">SQL Server syntax</a> for converting a date to a string. In MySQL you can use the <a href="http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date" rel="noreferrer">DATE</a> function to extract the date from a datetime:

<pre><code>SELECT *
FROM players
WHERE DATE(us_reg_date) BETWEEN '2000-07-05' AND '2011-11-10'
</code></pre>

But if you want to take advantage of an index on the column <code>us_reg_date</code> you might want to try this instead:

<pre><code>SELECT *
FROM players
WHERE us_reg_date &gt;= '2000-07-05'
 AND us_reg_date &lt; '2011-11-10' + interval 1 day
</code></pre>

blocks|key|3238141|text|你可以尝试下面的查询，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3238142|select+*+from+players
where+
++++us_reg_date+between+'2000-07-05'
and
++++DATE_ADD('2011-11-10',INTERVAL+1+DAY)|code-block|syntax|javascript|3238143|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|$]]

You can try below query,

<pre><code>select * from players
where 
 us_reg_date between '2000-07-05'
and
 DATE_ADD('2011-11-10',INTERVAL 1 DAY)
</code></pre>

blocks|key|3238164|text|这对我来说很有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3238165|select+date_format(date(starttime),'%25Y-%25m-%25d')+from+data
where+date(starttime)+>=+date+'2012-11-02';|code-block|syntax|javascript|3238166|注意格式字符串'%25Y-%25m-%25d‘和输入日期的格式。|3238167|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

This works for me:

<pre><code>select date_format(date(starttime),'%Y-%m-%d') from data
where date(starttime) &gt;= date '2012-11-02';
</code></pre>

Note the format string '%Y-%m-%d' and the format of the input date.

blocks|key|3023736|text|我知道答案了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3023737|代码如下：|3023738|SELECT+*+FROM+table
WHERE+STR_TO_DATE(column,+'%25d/%25m/%25Y')
++BETWEEN+STR_TO_DATE('29/01/15',+'%25d/%25m/%25Y')
++++AND+STR_TO_DATE('07/10/15',+'%25d/%25m/%25Y')|code-block|syntax|javascript|3023739|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I got the answer.
Here is the code:
<pre><code>SELECT * FROM table
WHERE STR_TO_DATE(column, '%d/%m/%Y')
 BETWEEN STR_TO_DATE('29/01/15', '%d/%m/%Y')
 AND STR_TO_DATE('07/10/15', '%d/%m/%Y')
</code></pre>

blocks|key|3238192|text|这就是它对我的作用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3238193|select+*+from+table
where+column
BETWEEN+STR_TO_DATE('29/01/15',+'%25d/%25m/%25Y')
+AND+STR_TO_DATE('07/10/15',+'%25d/%25m/%25Y')|code-block|syntax|javascript|3238194|请注意，我必须从以前的解决方案中更改STR_TO_DATE(列，'%25d/%25m/%25Y')，因为它需要很长时间才能加载|3238195|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

this is what it worked for me:

<pre><code>select * from table
where column
BETWEEN STR_TO_DATE('29/01/15', '%d/%m/%Y')
 AND STR_TO_DATE('07/10/15', '%d/%m/%Y')
</code></pre>

Please, note that I had to change STR_TO_DATE(column, '%d/%m/%Y') from previous solutions, as it was taking ages to load

I want to compare a date from a database that is between 2 given dates.
The column from the database is DATETIME, and I want to compare it only to the date format, not the datetime format.

<pre><code>SELECT * FROM `players` WHERE CONVERT(CHAR(10),us_reg_date,120) &gt;= '2000-07-05' AND CONVERT(CHAR(10),us_reg_date,120) &lt;= '2011-11-10'
</code></pre>

I get this error when I execute the SQL above:

<blockquote>
 You have an error in your SQL syntax;
 check the manual that corresponds to
 your MySQL server version for the
 right syntax to use near
 'us_reg_date,120) >= '2000-07-05' AND
 CONVERT(CHAR(10),us_reg_date,120) &lt;=
 '2011-' at line 1
</blockquote>

How can this problem be fixed?

Compare dates in MySQL

我想从一个数据库中比较一个日期，它在2个给定的日期之间。数据库中的列是DATETIME，我只想将它与date格式进行比较，而不是datetime格式。SELECT * FROM `players` WHERE CONVERT(CHAR(10),us_reg_date,120) >= '2000-07-05' AND C...

问比较MySQL中的日期
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问比较MySQL中的日期EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问比较MySQL中的日期
EN