blocks|key|291064|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|291065|，但这显然是在执行'string‘比较|blockquote|291066|291067|291068|不是的。该字符串将自动转换为日期时间值。|291069|请参阅11.2.+Type+Conversion+in+Expression+Evaluation.|offset|length|291070|291071|当运算符与不同类型的操作数一起使用时，会进行类型转换以使操作数兼容。有些转换是隐式发生的。例如，根据需要，MySQL会自动将数字转换为字符串，反之亦然。|291072|291073|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html^0|0|0|0|0|0|3|1B|0|0|0|0|0^^$0|@$1|2|3|-4|4|5|6|X|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|Y|7|@]|8|@]|9|$]]|$1|D|3|-4|4|5|6|Z|7|@]|8|@]|9|$]]|$1|E|3|-4|4|5|6|10|7|@]|8|@]|9|$]]|$1|F|3|G|4|5|6|11|7|@]|8|@]|9|$]]|$1|H|3|I|4|5|6|12|7|@]|8|@$J|13|K|14|1|15]]|9|$]]|$1|L|3|-4|4|5|6|16|7|@]|8|@]|9|$]]|$1|M|3|N|4|C|6|17|7|@]|8|@]|9|$]]|$1|O|3|-4|4|5|6|18|7|@]|8|@]|9|$]]|$1|P|3|-4|4|5|6|19|7|@]|8|@]|9|$]]]|Q|$R|$4|S|T|U|9|$V|W]]]]

<blockquote>
 But this is obviously performing a 'string' comparison 
</blockquote>

No. The string will be automatically cast into a DATETIME value.

See <a href="http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html" rel="noreferrer">11.2. Type Conversion in Expression Evaluation.</a>

<blockquote>
 When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa.
</blockquote>

blocks|key|3229583|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3229584|+...this显然正在执行“字符串”比较|blockquote|3229585|3229586|3229587|否-如果日期/时间格式与支持的格式匹配，则MySQL将执行隐式转换，以根据要比较的列将值转换为日期时间。同样的事情也会发生在：|3229588|WHERE+int_column+=+'1'|code-block|syntax|javascript|3229589|...where将字符串值"1“转换为INTeger，因为int_column的数据类型是INT，而不是CHAR/VARCHAR/TEXT。|offset|length|style|CODE|3229590|如果您想显式地将字符串转换为日期时间，则STR_TO_DATE+function将是最佳选择：|3229591|WHERE+expires_at+<=+STR_TO_DATE('2010-10-15+10:00:00',+'%25Y-%25m-%25d+%25H:%25i:%25s')|3229592|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date^0|0|0|0|0|0|0|T|A|0|K|K|0|0|0^^$0|@$1|2|3|-4|4|5|6|14|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|15|7|@]|8|@]|9|$]]|$1|D|3|-4|4|5|6|16|7|@]|8|@]|9|$]]|$1|E|3|-4|4|5|6|17|7|@]|8|@]|9|$]]|$1|F|3|G|4|5|6|18|7|@]|8|@]|9|$]]|$1|H|3|I|4|J|6|19|7|@]|8|@]|9|$K|L]]|$1|M|3|N|4|5|6|1A|7|@$O|1B|P|1C|Q|R]]|8|@]|9|$]]|$1|S|3|T|4|5|6|1D|7|@]|8|@$O|1E|P|1F|1|1G]]|9|$]]|$1|U|3|V|4|J|6|1H|7|@]|8|@]|9|$K|L]]|$1|W|3|-4|4|5|6|1I|7|@]|8|@]|9|$]]]|X|$Y|$4|Z|10|11|9|$12|13]]]]

<blockquote>
 ...this is obviously performing a 'string' comparison 
</blockquote>

No - if the date/time format matches the supported format, MySQL performs implicit conversion to convert the value to a DATETIME, based on the column it is being compared to. Same thing happens with:

<pre><code>WHERE int_column = '1'
</code></pre>

...where the string value of "1" is converted to an INTeger because <code>int_column</code>'s data type is INT, not CHAR/VARCHAR/TEXT.

If you want to explicitly convert the string to a DATETIME, the <a href="http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date" rel="noreferrer">STR_TO_DATE function</a> would be the best choice:

<pre><code>WHERE expires_at &lt;= STR_TO_DATE('2010-10-15 10:00:00', '%Y-%m-%d %H:%i:%s')
</code></pre>

blocks|key|296131|text|我知道它很老，但我只是遇到了这个问题，下面是我在SQL文档中看到的内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|296132|296133|在使用BETWEEN和date或time值时，为获得最佳结果，请使用CAST()将这些值显式转换为所需的数据类型。示例:如果将日期时间与两个日期值进行比较，请将日期值转换为日期时间值。如果在与日期的比较中使用诸如'2001-1-1‘之类的字符串常量，请将字符串转换为日期。|blockquote|296134|296135|296136|我认为使用STR_TO_DATE更好，因为他们花了时间制作了一个函数，而且我在文档之间找到了这个……|296137|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|M|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|N|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|O|8|@]|9|@]|A|$]]|$1|G|3|-4|5|6|7|P|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|Q|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|K|$]]

I know its pretty old but I just encounter the problem and there is what I saw in the SQL doc : 

<blockquote>
 [For best results when using BETWEEN with date or time values,] use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE.
</blockquote>

I assume it's better to use STR_TO_DATE since they took the time to make a function just for that and also the fact that i found this in the BETWEEN doc...

For example the following query works fine:

<pre><code>SELECT * 
 FROM quotes 
 WHERE expires_at &lt;= '2010-10-15 10:00:00';
</code></pre>

But this is obviously performing a 'string' comparison - I was wondering if there was a function built in to MySQL that specifically does 'datetime' comparisons.

mysql datetime comparison

MySQL

例如，下面的查询运行良好：SELECT * FROM quotes WHERE expires_at <= '2010-10-15 10:00:00';但这显然是在执行“字符串”比较--我想知道MySQL中是否有专门执行“日期时间”比较的函数。

问mysql日期时间比较
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql日期时间比较EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql日期时间比较
EN