job.answiz.com
  • 1
Votes
name

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.

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
  • 1
Reply Report