Mysql 日期功能不工作少于

Mysql date function not working for less than

提问by Gihan Dilusha

I need to get all records those equal and less than 2012-12-28 i used bellow query for this, booking_time is DATETIME field, and there are records less than 2012-12-28 but it returns zero rows. does anyone has idea ?

我需要获取等于和小于 2012-12-28 的所有记录我为此使用了波纹管查询，booking_time 是 DATETIME 字段，并且有小于 2012-12-28 的记录但它返回零行。有没有人有想法？

SELECT * FROM ctx_bookings WHERE DATE(booking_time)<=2012-12-28 ORDER BY id ASC

Table filed

表归档

+---------------------+
| booking_time        |
+---------------------+
| 2012-12-20 03:10:09 |
| 2012-12-25 02:10:04 |
+---------------------+

Please anybody know why is this happening ?

请问有人知道为什么会这样吗？

回答by John Woo

wrap the value with single quote and surely it will work

用单引号包裹值，它肯定会起作用

SELECT * 
FROM ctx_bookings 
WHERE DATE(booking_time) <= '2012-12-28' 
ORDER BY id ASC

• SQLFiddle Demo

• SQLFiddle 演示

回答by eggyal

As documented under Date and Time Literals:

如日期和时间文字所述：

MySQL recognizes DATEvalues in these formats:

• As a string in either 'YYYY-MM-DD'or 'YY-MM-DD'format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31'are equivalent.

• As a string with no delimiters in either 'YYYYMMDD'or 'YYMMDD'format, provided that the string makes sense as a date. For example, '20070523'and '070523'are interpreted as '2007-05-23', but '071332'is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.

• As a number in either YYYYMMDDor YYMMDDformat, provided that the number makes sense as a date. For example, 19830905and 830905are interpreted as '1983-09-05'.

MySQL 识别DATE以下格式的值：

• 作为'YYYY-MM-DD'或'YY-MM-DD'格式的字符串。允许使用“宽松”语法：任何标点字符都可以用作日期部分之间的分隔符。例如，'2012-12-31'，'2012/12/31'，'2012^12^31'，和'2012@12@31'是相等的。

• 作为没有分隔符'YYYYMMDD'或'YYMMDD'格式的字符串，前提是该字符串作为日期有意义。例如，'20070523'和'070523'被解释为'2007-05-23'，但是'071332'是非法的（它有无意义的月份和日期部分）并变为'0000-00-00'。

• 作为YYYYMMDD或YYMMDD格式的数字，前提是该数字作为日期有意义。例如，19830905和830905被解释为'1983-09-05'。

As @Barmar commented, your literal expression 2012-12-28is evaluated as the arithmetic (2012 - 12) - 28, which equals 1,972.

正如@Barmar 评论的那样，您的文字表达式2012-12-28被计算为算术(2012 - 12) - 28，等于 1,972。

Per @JW.'s answer, you can quote that expression to obtain a valid date literal (of the first form, above). Alternatively:

根据@JW.'s answer，您可以引用该表达式以获得有效的日期文字（上面的第一种形式）。或者：

• whilst still quoting the literal, you could use any other punctuation character (or even no character) as the delimiter between date parts:

  WHERE DATE(booking_time) <= '2012_12_28'
  WHERE DATE(booking_time) <= '20121228'
  

• you could remove the delimiters and leave your literal unquoted:

  WHERE DATE(booking_time) <= 20121228
  

• 在仍然引用文字的同时，您可以使用任何其他标点符号（甚至没有字符）作为日期部分之间的分隔符：

  WHERE DATE(booking_time) <= '2012_12_28'
  WHERE DATE(booking_time) <= '20121228'
  

• 您可以删除分隔符并保留您的文字不加引号：

  WHERE DATE(booking_time) <= 20121228
  

Note also that using a filter criterion like this, which uses a function (in this case, the DATE()function) over a column, requires a full table scan in order to evaluate that function—it therefore will not benefit from any indexes. A more sargable alternative would be to filter more explicitly over the range of column values (i.e. times) that satisfy your criteria:

还要注意，使用这样的过滤条件，它在列上使用函数（在本例中为DATE()函数），需要全表扫描才能评估该函数——因此它不会从任何索引中受益。更可靠的替代方法是更明确地过滤满足您的条件的列值范围（即时间）：

WHERE booking_time < '2012-12-28' + INTERVAL 1 DAY

This is equivalent because any time that falls strictly prior to the following day will necessarily have occurred on or before the day of interest. It is sargable because the column is compared to a constant expression (the result of the +operation being deterministic), and therefore an index over booking_timecan be traversed to immediately find all matching records.

这是等效的，因为任何严格落在第二天之前的时间都必然发生在感兴趣的当天或之前。它是 sargable，因为该列与常量表达式（+操作的结果是确定性的）进行比较，因此booking_time可以遍历索引以立即找到所有匹配的记录。

回答by Nipun Jain

 SELECT * FROM ctx_bookings WHERE DATE(booking_time)<='2012-12-28' ORDER BY id ASC

try this mate

试试这个伙伴