Menu
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?
357
This can be easily done using equals to(=), less than(<), and greater than(>) operators. In SQL, the date value has DATE datatype which accepts date in 'yyyy-mm-dd' format. To compare two dates, we will declare two dates and compare them using the IF-ELSE statement.
MySQL has the ability to compare two different dates written as a string expression. When you need to compare dates between a date column and an arbitrary date, you can use the DATE() function to extract the date part from your column and compare it with a string that represents your desired date.
The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format. The supported range is '1000-01-01' to '9999-12-31' . The DATETIME type is used for values that contain both date and time parts.
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' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
