Miguel Fernandez Miguel Fernandez - 2 months ago 5
MySQL Question

Select depending CURDATE MySQL

I have the following query:

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 = 'En aprobacion' OR STATUS = 'Activa')
AND CloseDate < CURDATE()


As a result of this query I have the following table:

Result

Everything it's ok.

Now, the problem is that I 'm going to use that query in my website, I create a JavaScript function which is going to be executed every 5th and every 19th of a month. My question, what can I do to only select the row which his CloseDate is <= yyyy-mm-07 or CloseDate >= yyyy-mm-07 and <= yyyy-mm-21 depending of CURDATE(5th or 19th), example:


Curdate = yyyy-mm-05.

Expected result: Data from Id 00003


What I tried was adding another AND to the query, something like this:

AND CloseDate < CURDATE() AND CloseDate <= '2016-09-07'


If my question is not much clear let me explain with this, I want to select CloseDate '2016-09-07' when curdate = '2016-whatever-05, 06', 07 or select CloseDate '2016-09-21' where curdate = '2016-whatever-19, 20, 21'.

Answer

Don't know if I understood your problem correctly but check the DAYOFMONTH() or simply DAY() function.

Returns the day of the month for date, in the range 1 to 31, or 0 for dates such as '0000-00-00' or '2008-00-00' that have a zero day part.

You can use it like this

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 = 'En aprobacion' OR STATUS = 'Activa')
    AND (CASE 
            WHEN DAY(CURDATE()) = '5' AND DATE(CloseDate) <= DATE_FORMAT(NOW() ,'%Y-%m-07') 
            THEN 1
            WHEN DAY(CURDATE()) = '19' AND (DATE(CloseDate) >= DATE_FORMAT(NOW() ,'%Y-%m-07') AND DATE(CloseDate) <= DATE_FORMAT(NOW() ,'%Y-%m-21'))
            THEN 1
            ELSE 0
            END)