Mysql date function not working for less than
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
orYYMMDD
format, provided that the number makes sense as a date. For example,19830905
and830905
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.
SELECT * FROM ctx_bookings WHERE DATE(booking_time)<='2012-12-28' ORDER BY id ASC
try this mate
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