Tiny Tiny - 2 months ago 9
MySQL Question

Comparing dates in MySQL ignoring time portion of a DateTime field

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

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'