Pick up only the days of the current month

Question

Pick up only the days of the current month

Navigation

#1 by (3 votes)

2

I have the query below where it is done on the current day, how could I make the same query in the current month until today, example: 01/09 13/09 but so that the query tomorrow 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

Field referring to date: 2016-09-13 15:27:16

mysql query

asked by anonymous 13.09.2016 / 23:49

1 answer

Slow Search Ignoring Accents MYSQL - Using SUM with Join

score 3 · Accepted Answer

To filter a range, you should use between .

The end date is CURDATE() , which is the current date of the MySQL server.

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

In your example, replace:

HAVING DATE(DT_FECHAMENTO) =  CURDATE()

By:

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