User1899289003 User1899289003 - 1 year ago 64
SQL Question

How to compare two dates MySQL

I'm trying to create a report function, I store in my database some data with a specific value (closeDate), this value can be only 7th or 21nd, here is an example of my table:

Table Example

What I want to do is every 22nd execute a query to select all the register which have Status = "Active" or "On approve" and CloseDate < to 22nd (Curdate). I have this query, but this query show me all exactly like in the image and what I expect is that the query show me only the data from Id = 00001 and 00003 because Id 00002 CloseDate(2016-10-21) it's not < to 2016-09-22 (curdate).

SELECT tbl_usuarios.Correo, tbl_alertas.Id, tbl_alertas.Purpose, tbl_alertas.Status, tbl_alertas.OpenDate, tbl_alertas.CloseDate, tbl_alertas.Owner, tbl_alertas.ValueStream, tbl_alertas.Family
FROM tbl_usuarios
INNER JOIN tbl_alertas ON tbl_usuarios.Nombre = tbl_alertas.Owner
WHERE STATUS = 'On approve' OR STATUS = 'Active' AND CloseDate < CURDATE()

What I'm doing wrong?

Answer Source

You may need to rearrange your clauses in your WHERE condition. It has both OR and AND so it may not be interpreted as you expect.

I would try something like this:

WHERE (STATUS = 'On approve' OR STATUS = 'Active') AND CloseDate < CURDATE() 

Or you can simplify this using IN:

WHERE STATUS IN ('On approve', 'Active') AND CloseDate < CURDATE() 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download