Giovani Madeira 0 Posted December 13, 2020 Report Share Posted December 13, 2020 Boa noite, Tenho a seguinte tabela Firebird 3.0 CREATE TABLE CADASTRO ( CODALU Integer NOT NULL, ALUNO Varchar(40), CPF Varchar(15), RG Varchar(10), ENDERECO Varchar(50), CIDADE Varchar(30), TELEFONE Varchar(20), EMAIL Varchar(40), DATA_CAD Timestamp, PRIMARY KEY (CODALU) ); Quero fazer esta consulta para trazer o numero de alunos cadastrados no mes: Select Count(codalu) as Total from CADASTRO Where DATA_CAD >= '''+'01'+Copy(DateToStr(Now),3,8)+''' '); Onde esta o erro, Grato Link to post Share on other sites
leandroaoa 68 Posted December 13, 2020 Report Share Posted December 13, 2020 8 horas atrás, Giovani Madeira disse: Boa noite, Tenho a seguinte tabela Firebird 3.0 CREATE TABLE CADASTRO ( CODALU Integer NOT NULL, ALUNO Varchar(40), CPF Varchar(15), RG Varchar(10), ENDERECO Varchar(50), CIDADE Varchar(30), TELEFONE Varchar(20), EMAIL Varchar(40), DATA_CAD Timestamp, PRIMARY KEY (CODALU) ); Quero fazer esta consulta para trazer o numero de alunos cadastrados no mes: Select Count(codalu) as Total from CADASTRO Where DATA_CAD >= '''+'01'+Copy(DateToStr(Now),3,8)+''' '); Onde esta o erro, Grato seu select esta errado >= ira trazer todos o meses mair que a data informada voce tem que pesquisar entre conforme abaixo where data_cad between '2020-11-01' and '2020-11-30' Link to post Share on other sites
BigWings 3,981 Posted December 13, 2020 Report Share Posted December 13, 2020 Também pode usar a função EXTRACT. SELECT * FROM CADASTRO WHERE EXTRACT(MONTH FROM DATA_CAD) = :MES AND EXTRACT(YEAR FROM DATA_CAD) = :ANO BigWingsAjude o Projeto ACBr crescer - Assine o SAC Link to post Share on other sites
Giovani Madeira 0 Posted December 13, 2020 Author Report Share Posted December 13, 2020 10 horas atrás, leandroaoa disse: seu select esta errado >= ira trazer todos o meses mair que a data informada voce tem que pesquisar entre conforme abaixo where data_cad between '2020-11-01' and '2020-11-30' Grato, só que preciso contar os alunos cadastrados no mes atual Link to post Share on other sites
leandroaoa 68 Posted December 14, 2020 Report Share Posted December 14, 2020 Em 12/12/2020 at 23:20, Giovani Madeira disse: Boa noite, Tenho a seguinte tabela Firebird 3.0 CREATE TABLE CADASTRO ( CODALU Integer NOT NULL, ALUNO Varchar(40), CPF Varchar(15), RG Varchar(10), ENDERECO Varchar(50), CIDADE Varchar(30), TELEFONE Varchar(20), EMAIL Varchar(40), DATA_CAD Timestamp, PRIMARY KEY (CODALU) ); Quero fazer esta consulta para trazer o numero de alunos cadastrados no mes: Select Count(codalu) as Total from CADASTRO Where DATA_CAD >= '''+'01'+Copy(DateToStr(Now),3,8)+''' '); Onde esta o erro, Grato nao trabalho com Firebird mais vamos la Select Count(codalu) as Total from CADASTRO WHERE EXTRACT(MONTH FROM DATA_CAD) = EXTRACT(MONTH FROM NOW) AND EXTRACT(YEAR FROM DATA_CAD) = EXTRACT(YEAR FROM NOW) Link to post Share on other sites
Giovani Madeira 0 Posted December 14, 2020 Author Report Share Posted December 14, 2020 Select Count(codalu) as Total from CADASTRO WHERE EXTRACT(MONTH FROM DATA_CAD) = EXTRACT(MONTH FROM NOW) AND EXTRACT(YEAR FROM DATA_CAD) = EXTRACT(YEAR FROM NOW) Dá esse erro no Firebird Preparing query: Select Count(codalu) as Total from CADASTRO WHERE EXTRACT(MONTH FROM DATA_CAD) = EXTRACT(MONTH FROM NOW) Error: *** IBPP::SQLException *** Context: Statement::Prepare( Select Count(codalu) as Total from CADASTRO WHERE EXTRACT(MONTH FROM DATA_CAD) = EXTRACT(MONTH FROM NOW) ) Message: isc_dsql_prepare failed SQL Message : -206 can't format message 13:794 -- message file C:\Windows\SYSTEM32\firebird.msg not found Engine Code : 335544569 Engine Message : Dynamic SQL Error SQL error code = -206 Column unknown NOW At line 2, column 57 Total execution time: 0.006s Link to post Share on other sites
BigWings 3,981 Posted December 14, 2020 Report Share Posted December 14, 2020 No lugar de NOW use current_timestamp ou current_date. BigWingsAjude o Projeto ACBr crescer - Assine o SAC Link to post Share on other sites
Giovani Madeira 0 Posted December 15, 2020 Author Report Share Posted December 15, 2020 8 horas atrás, BigWings disse: No lugar de NOW use current_timestamp ou current_date. Grato funcionou Link to post Share on other sites
Juliomar Marchetti 3,845 Posted December 15, 2020 Report Share Posted December 15, 2020 Obrigado por reportar. Fechando. Para novas dúvidas, criar um novo tópico. Juliomar Marchetti Ajude o Projeto ACBr crescer - Assine o SAC skype: juliomar telegram: juliomar http://www.juliomarmarchetti.com.br Projeto ACBr - A maior comunidade Open Source de Automação Comercial do Brasil Link to post Share on other sites
Recommended Posts