Mysql date function not working for less than

Question

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 ?

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 ?

Solution 1

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

Solution 2

As documented under Date and Time Literals:

MySQL recognizes DATE values 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 YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.

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

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

• 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
  

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:

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_time can be traversed to immediately find all matching records.

Solution 3

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

try this mate

Comments

[Barmar]

2012-12-28 is an arithmetic expression, which equals 1972.