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?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
You could use the
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
SELECT col1, col2, ..., coln FROM order_table WHERE order_date >= '2012-05-03' AND order_date < '2012-05-04'
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.
@Mark has got the good approach but just be careful that you will always have to calculate and find next day in that case. If you want to avoid that and still ignore time you could do following:
WHERE order_date >= '2012-05-27 00:00:00' AND order_date <= '2012-05-27 23:59:59'
I hope this makes sense.
SELECT * FROM order_table WHERE date(order_date) = '2012-05-03';
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';
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')
one of the following could be used when comparing dates in MySQL:
DATEDIFF() Subtract two dates TIMEDIFF() Subtract time TIMESTAMPDIFF() Subtract an interval from a datetime expression PERIOD_DIFF() Return the number of months between periods
for more on date functions MySQL documentation.
In my case i had ,
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')