CODEWITHSUNDEEP
mysqldatedatetimedate-comparison
Tiny
Tiny

Reputation: 27899

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?

Upvotes: 56

Views: 68228

Answers (9)

Svetoslav Trifonov
Svetoslav Trifonov

Reputation: 1

You can also do it something like that, if you want to keep formatting as it is.

select DATE_FORMAT(order_date, '%d/%m/%Y') as order_date from order_table  
    where DATE_FORMAT(order_date, '%d/%m/%Y') >= '18/04/2023'

or

 select DATE_FORMAT(order_date, '%d/%m/%Y') as order_date from order_table  
        where DATE_FORMAT(order_date, '%d/%m/%Y') between '18/04/2023' and 
        '20/04/2023'

for example.

Upvotes: 0

shareef
shareef

Reputation: 9581

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')

Upvotes: 0

Erevodifosin
Erevodifosin

Reputation: 31

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';

Upvotes: 3

Augustas
Augustas

Reputation: 1217

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.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838216

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'

Upvotes: 93

James Black
James Black

Reputation: 41858

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.

Upvotes: 9

Kotu
Kotu

Reputation: 1102

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')

Upvotes: 3

deej
deej

Reputation: 2564

@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.

Upvotes: 6

lanzz
lanzz

Reputation: 43168

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

Upvotes: 6

Related Questions