Pick up only the days of the current month

Question

I have the query below where it is made on the current day, how could make the same query in the current month until today, example: 01/09 to 13/09, but so that tomorrow’s consultation is: 01/09 to 14/09 and so on.

   SELECT 
        o.numero AS CHAMADO,
        p.problema AS PROBLEMA,
        o.descricao AS DESCRICAO,
        o.contato AS CONTATO,
        o.telefone AS TELEFONE,
        o.data_abertura AS DATA_DE_ABERTURA,
        TIMESTAMPDIFF (HOUR, str_to_date(data_abertura,'%Y-%m-%d'), CURDATE()) as HORAS_EM_ABERTO,
        i.inst_nome AS UNIDADE,
        a.sistema AS AREA,
        l.local AS SETOR,
        u.nome AS TECNICO,
        ua.nome AS ABERTO_POR,
        o.data_fechamento DT_FECHAMENTO,
        s.status AS STATUS
    FROM
        ocorrencias AS o
            LEFT JOIN
        sistemas AS a ON a.sis_id = o.sistema
            LEFT JOIN
        localizacao AS l ON l.loc_id = o.local
            LEFT JOIN
        instituicao AS i ON i.inst_cod = o.instituicao
            LEFT JOIN
        usuarios AS u ON u.user_id = o.operador
            LEFT JOIN
        usuarios AS ua ON ua.user_id = o.aberto_por
            LEFT JOIN
        `status` AS s ON s.stat_id = o.status
            LEFT JOIN
        status_categ AS stc ON stc.stc_cod = s.stat_cat
            LEFT JOIN
        problemas AS p ON p.prob_id = o.problema
            LEFT JOIN
        sla_solucao AS sls ON sls.slas_cod = p.prob_sla
            LEFT JOIN
        prioridades AS pr ON pr.prior_cod = l.loc_prior
            LEFT JOIN
        sla_solucao AS slr ON slr.slas_cod = pr.prior_sla
            LEFT JOIN
        script_solution AS sol ON sol.script_cod = o.oco_script_sol
            LEFT JOIN
        prior_atend AS prioridade_atendimento ON prioridade_atendimento.pr_cod = o.oco_prior
                  HAVING DATE(DT_FECHAMENTO) between  DATE_FORMAT(CURDATE() ,'%Y-%m-01') AND CURDATE() 
            AND a.sistema = 'Ti'

    ORDER BY numero ASC

Date field: 2016-09-13 15:27:16

Answer 1

To filter a range, you must use the between.

The final date is CURDATE(), which is the current date of the Mysql server.

The initial date you set in 01 and uses the current month and year and formats with the function DATE_FORMAT: DATE_FORMAT(CURDATE() ,'%Y-%m-01').

In your example, replace:

HAVING DATE(DT_FECHAMENTO) =  CURDATE()

For:

HAVING DATE(DT_FECHAMENTO) between  DATE_FORMAT(CURDATE() ,'%Y-%m-01') AND CURDATE()