CODEWITHSUNDEEP
mysqlsql
User1899289003
User1899289003

Reputation: 872

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?

Upvotes: 2

Views: 905

Answers (2)

Rahul Patel
Rahul Patel

Reputation: 5246

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 DATE(CloseDate) < CURDATE()

Upvotes: 0

gen_Eric
gen_Eric

Reputation: 227270

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

Upvotes: 1

Related Questions