DATE() is a MySQL function that extracts only the date part of a date or date/time expression
SELECT * FROM table_name WHERE DATE(date_field) BETWEEN '2016-12-01' AND '2016-12-10';
You can do it manually, by comparing with greater than or equal and less than or equal.
select * from table_name where created_at_column >= lower_date and created_at_column <= upper_date;
In our example, we need to retrieve data from a particular day to day. We will compare from the beginning of the day to the latest second in another day.
select * from table_name where created_at_column >= '2018-09-01 00:00:00' and created_at_column <= '2018-09-05 23:59:59';
Is date_field of type datetime? Also you need to put the eariler date first.
It should be:
SELECT * FROM `objects`
WHERE (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')
Might be a problem with date configuration on server side or on client side. I've found this to be a common problem on multiple databases when the host is configured in spanish, french or whatever... that could affect the format dd/mm/yyyy or mm/dd/yyyy.
Your query should have date as
select * from table between `lowerdate` and `upperdate`
try
SELECT * FROM `objects`
WHERE (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')
As extension to the answer from @sabin and a hint if one wants to compare the date part only (without the time):
If the field to compare is from type datetime and only dates are specified for comparison, then these dates are internally converted to datetime values. This means that the following query
SELECT * FROM `objects` WHERE (date_time_field BETWEEN '2010-01-30' AND '2010-09-29')
will be converted to
SELECT * FROM `objects` WHERE (date_time_field BETWEEN '2010-01-30 00:00:00' AND '2010-09-29 00:00:00')
internally.
This in turn leads to a result that does not include the objects from 2010-09-29 with a time value greater than 00:00:00!
Thus, if all objects with date 2010-09-29 should be included too, the field to compare has to be converted to a date:
SELECT * FROM `objects` WHERE (DATE(date_time_field) BETWEEN '2010-01-30' AND '2010-09-29')
Try switching the dates around:
2010-09-29 > 2010-01-30?
Just Cast date_field as date
SELECT * FROM `objects`
WHERE (cast(date_field as date) BETWEEN '2010-09-29' AND
'2010-01-30' )
When using Date and Time values, you must cast the fields as DateTime and not Date.
Try :
SELECT * FROM `objects`
WHERE (CAST(date_field AS DATETIME)
BETWEEN CAST('2010-09-29 10:15:55' AS DATETIME) AND CAST('2010-01-30 14:15:55' AS DATETIME))
Source: Stackoverflow.com