CODEWITHSUNDEEP
mysqlsqlselectlogical-orlogical-and
abhi chavda
abhi chavda

Reputation: 173

mysql : date format not working with OR in where condition

Whenever I'm using OR in where condition my query is putting date_format() it's working but when I'm using AND it's working fine.

True Query:

SELECT * FROM `tbl_inquiry_trans`
WHERE date_format(follow_updatetime,'%Y-%m-%d') >= '2018-08-02'
    AND date_format(follow_updatetime,'%Y-%m-%d') <= '2018-08-02'
    AND emp_id=2 or user_id=2

The above query should display specific date data but it's showing all dates data.

Test Query:

SELECT * FROM `tbl_inquiry_trans`
WHERE date_format(follow_updatetime,'%Y-%m-%d') >= '2018-08-02'
    AND date_format(follow_updatetime,'%Y-%m-%d') <= '2018-08-02'
    AND emp_id=2

When I'm using AND it's showing expected date data but I want to use OR in the where clause.

Upvotes: 1

Views: 2628

Answers (2)

Mureinik
Mureinik

Reputation: 311723

The and logical operator has a higher precedence than the or operator (i.e., and expressions are evaluated before or expressions, in a similar way you'd calculate a multiplication before calculating an addition in an arithmetic expression). In order to achieve the behavior you wanted, you need to surround the two sides of the or operator with parenthesis:

SELECT * 
FROM   tbl_inquiry_trans 
WHERE  date_format(follow_updatetime,'%Y-%m-%d')>='2018-08-02' AND 
       date_format(follow_updatetime,'%Y-%m-%d')<='2018-08-02' AND 
       (emp_id=2 OR user_id=2) -- Here

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521794

Same answer as @Mureinik, except that I don't think you need to those calls to DATE_FORMAT, because in MySQL it is possible to directly compare dates against string literals. So, the following should suffice:

SELECT *
FROM tbl_inquiry_trans
WHERE
    follow_updatetime >= '2018-08-02' AND follow_updatetime < '2018-08-03' AND
    (emp_id = 2 OR user_id = 2);

The logic in the above check on follow_updatetime is that any date would match if it were on or after midnight of 2018-08-02 or strictly before midnight of 2018-08-03. This would cover the entire day of 2018-08-02. This version of doing it is preferable to what you had, because it makes it possible to use an index on the follow_updatetime column.

Upvotes: 1

Related Questions