Comparing dates in MySQL ignoring time portion of

Question

I need to compare dates in MySQL ignoring the time portion in a DateTime column. I have tried the following SQL.

SELECT * FROM order_table where order_date=date_format('2012-05-03', '%Y-%m-%d');

It doesn't retrieve any row even though there is a date 2012-05-03 10:16:46 in MySQL table. How can the time portion in the DateTime field be ignored while comparing dates in MySQL?

Answer 1

SELECT * FROM order_table WHERE date(order_date) = '2012-05-03';

Answer 2

If you want to pass in a date then you can try something like this:

where YEAR(order_date)='2012' AND MONTH(order_date)='05' AND DAY(order_date)='03'

You can look at this for more functions.

Answer 3

You could use the DATE function:

SELECT col1, col2, ..., coln
FROM order_table
WHERE date(order_date) = '2012-05-03'

But this is more efficient, if your table is large and you have an index on order date:

SELECT col1, col2, ..., coln
FROM order_table
WHERE order_date >= '2012-05-03'
AND order_date < '2012-05-04'

Answer 4

Maybe, you can use function like date(). But in this case speed can be decreased, because index cannot be using. So, I recommend to use

SELECT * FROM order_table 
WHERE order_date between('2012-05-03 0:0:0' and '2012-05-03 23:59:59')

Answer 5

In my case i had , DATE_FORMAT and CONVERT_TZ

The only one worked for me is this

DATE_FORMAT(CONVERT_TZ(`order_item`.created, '+0:00', '+2:00'), '%d/%m/%Y %H:%i') 

BETWEEN 
('12/02/2018 01:42') 
AND 
('12/02/2018 10:51')

Answer 6

This is bit old .... but a correct answer also is using the date_format at the column order_date like below:

SELECT * FROM order_table where date_format(order_date, '%Y-%m-%d')='2012-05-03';