Menu de navegação

[Joao HenriqueParticipante]

Bom dia Colegas.

Estou fazendo uma tratativa em uma consulta SQL para retornar registros somente do mês anterior, fiz um teste usando o seguinte select:

SELECT To_Char( Add_Months(To_Date(’01/’||’03/2020′,’DD/MM/YYYY’),-1),’MM/YYYY’)FROM DUAL

a consulta acima retornou o seguinte resultado: 02/2020, exatamente o que eu esperava, porém ao aplicar a mesma função em uma tabela que já possuo no banco cujo campo dh_avaliacao do tipo date a consulta retorna a data desconfigurada da seguinte forma:

SELECT To_Char(Add_Months(To_Date(dh_avaliacao,’dd/mm/yyyy’),-1),’mm/yyyy’)mes FROM pagu_avaliacao WHERE cd_avaliacao = 3459855

Resultado da Consulta: 02/0020 (formato incorreto).

Alguém pode me ajudar com esta consulta? Desde já agradeço.

 

[José Laurindo ChiappaModerador]

Blz ? Então, acredito ser ** EXTREMAMENTE SIMPLES ** a sua questão : quando vc faz TO_DATE de uma string (como é o caso do trecho onde vc escrever To_Date(’01/’||’03/2020′,’DD/MM/YYYY’),-1),’MM/YYYY’) tudo funciona Absolutamente nos conformes, normal… PORÉM, vc NUNCA, em MOMENTO ALGUM, DEVERIA SEQUER PENSAR em pedir TO_DATE em cima de uma coluna que JÁ É DATE, que foi EXATAMENTE O QUE VOCÊ FEZ no trecho onde escreveu to_Date(dh_avaliacao,’dd/mm/yyyy’),-1),’mm/yyyy’) !!!!!!! Absolutamente NÃO FAZ SENTIDO vc usar uma função que converte para DATE alguém que JÁ É DATE, isso SÓ PODE DAR ENROSCO!!!! O enrosco aqui é que a função TO_DATE foi PROGRAMADA para trabalhar com strings (ou em alguns casos números, NUNCA dates) – aí ao receber alguém que Já É DATE, ela vai tentar fazer uma conversão IMPLÍCITA para STRING, e quando se fala em conversão Implícita nem sempre o banco consegue acertar qual formato usar, ele vai estar ADIVINHANDO!!!!
A solução é MUITO SIMPLES, vc só precisa NÃO CONVERTER PARA DATE qeum JÁ é DATE… Vou montar uma tabela de testes :

=> primeiro, antes de codificar, vou alterar minha tool (sqlplus no caso) para que me exiba a data completa, só para podermos enxergar o que está acontecendo :

`SCOTT@xepdb1::CONTAINER=XEPDB1> ALTER SESSION SET NLS_DATE_FORMAT=’dd/mm/yyyy hh24:mi:ss’;

Sessão alterada.`

=> ok, aí crio e populo :

SCOTT@xepdb1::CONTAINER=XEPDB1>  CREATE TABLE pagu_avaliacao (cd_avaliacao number, dh_avaliacao date);

Tabela criada.

SCOTT@xepdb1::CONTAINER=XEPDB1>  insert into PAGU_AVALIACAO values('3459853', to_date('10/03/2020 13:14:00', 'dd/mm/yyyy hh24:mi:ss'));

1 linha criada.

SCOTT@xepdb1::CONTAINER=XEPDB1>  insert into PAGU_AVALIACAO values('3459853', to_date('10/02/2020 13:14:00', 'dd/mm/yyyy hh24:mi:ss'));

1 linha criada.

SCOTT@xepdb1::CONTAINER=XEPDB1>  insert into PAGU_AVALIACAO values('3459855', to_date('29/02/2020 10:09:08', 'dd/mm/yyyy hh24:mi:ss'));

1 linha criada.

SCOTT@xepdb1::CONTAINER=XEPDB1>  insert into PAGU_AVALIACAO values('3459855', to_date('10/03/2020 08:39:40', 'dd/mm/yyyy hh24:mi:ss'));

1 linha criada.

SCOTT@xepdb1::CONTAINER=XEPDB1>  insert into PAGU_AVALIACAO values('3459855', to_date('10/02/2020 09:29:40', 'dd/mm/yyyy hh24:mi:ss'));

1 linha criada.

SCOTT@xepdb1::CONTAINER=XEPDB1>

==> se eu quero CONVERTER a coluna DATE para a data do mês passado, eu SIMPLESMENTE uso o ADD_MONTHS(valordate, -1) :

SCOTT@xepdb1::CONTAINER=XEPDB1> select cd_avaliacao, dh_avaliacao, add_months(dh_avaliacao, -1) DATA_NO_MES_ANTERIOR from pagu_avaliacao;

CD_AVALIACAO DH_AVALIACAO        DATA_NO_MES_ANTERIO
------------ ------------------- -------------------
     3459853 10/03/2020 13:14:00 10/02/2020 13:14:00
     3459853 10/02/2020 13:14:00 10/01/2020 13:14:00
     3459855 29/02/2020 10:09:08 31/01/2020 10:09:08
     3459855 10/03/2020 08:39:40 10/02/2020 08:39:40
     3459855 10/02/2020 09:29:40 10/01/2020 09:29:40

5 linhas selecionadas.

SCOTT@xepdb1::CONTAINER=XEPDB1>

==> Veja que o ADD_MONTHS respeita o dia : uma data de dia 10 do mês 03 é convertida para dia 10 do mês 02….SE FOSSE ISSO que vc quisesse fazer ok, tá pronto….
Porém, no início da thread vc disse que queria “retornar registros somente do mês anterior”, então IMAGINO que não é isso que vc quer , eu IMAGINO que na verdade vc quer conveter a data DE HOJE para o mês anterior IGNORANDO o dia do mês, deixando 01 como o dia do mês, E então aí sim, comparar esse dia 01 do mês anterior à hoje com a DH_AVALIACAO também ignorando o dia …
==> Para vc pegar o mês anterior da data de hoje mas truncando para dia 01 simplesmente uso o TRUNC, feito para isso :

SCOTT@xepdb1::CONTAINER=XEPDB1> select TRUNC(add_months(SYSDATE, -1), 'MONTH') MES_ANTERIOR_NO_DIA_01 from dual;

MES_ANTERIOR_NO_DIA
-------------------
01/02/2020 00:00:00

1 linha selecionada.

SCOTT@xepdb1::CONTAINER=XEPDB1>  

==> PRONTO, é só juntar as duas coisas no WHERE, e além disso vou INCLUIR a condição de filtrar os registros com cd_avaliacao = 3459855 :

SCOTT@xepdb1::CONTAINER=XEPDB1> ed
Gravou file afiedt.buf

  1  select CD_AVALIACAO, DH_AVALIACAO
  2   from PAGU_AVALIACAO
  3  WHERE cd_avaliacao = 3459855
  4*   AND trunc(dh_avaliacao, 'MONTH') = TRUNC(add_months(SYSDATE, -1), 'MONTH')
SCOTT@xepdb1::CONTAINER=XEPDB1> /

CD_AVALIACAO DH_AVALIACAO
------------ -------------------
     3459855 29/02/2020 10:09:08
     3459855 10/02/2020 09:29:40

2 linhas selecionadas.

SCOTT@xepdb1::CONTAINER=XEPDB1>

==> taí : nos estamos em março é o filtro só retornou os dois registros onde DH_AVALIACAO está no mês anterior (fevereiro nos dias 29 e dia 10) E onde cd_avaliacao = 3459855 …. O outro registro com CD_AVALIACAO=3459855 o de 10/03/2020 08:39:40) foi IGNORADO porque Não Se Encaixa no Mês passado, é o que vc queria….
Veja que a condição em si é UMA ÚNICA SIMPLES linha no WHERE, não tem como errar…. Eu fiz questão de MOSTRAR o passo-a-passo pra fins didáticos mas a lógica em si é MUITO simples….

Abraços,

Chiappa

[José Laurindo ChiappaModerador]

Obviamente, essa NEM DE LONGE é a única maneira : vc PODERIA usar o EXTRACT(MONTH, PODERIA converter a data para STRING e então via SUBSTR obter os dígitos do Mês e do ano cfrme necessário, entre N outras Opções….

Abraços,

Chiappa

[Joao HenriqueParticipante]

Ok Chiappa, beleza! entendi eu estava convertendo para to_date um campo que já estava criado na tabela como date, realmente apliquei a solução dada por você e deu certo, mais uma vez muito obrigado pela força!! Vivendo e aprendendo.

 

Um abraço.

João Henrique.

[José Laurindo ChiappaModerador]

Blz… Uma Obs final, que só tem Aplicabilidade se vc for fazer esse WHERE numa tabela com grandes volumes, é a seguinte : cfrme é sabido, uma função que modifica a coluna indexada PODE fazer com que um índice existente nessa coluna não seja usado, vide https://hoopercharles.wordpress.com/2010/03/08/impact-of-the-trunc-function-on-an-indexed-date-column/ por exemplo…
Assim, se teu Objetivo é filtrar os registros que se encaixam entre o dia 01 e o último dia do mês passado, AO invés de usar TRUNC na coluna da tabela talvez vc queira optar por um BETWEEN…. Pra isso, pra pegar o dia 01 do mês passado nós já sabemos como é que se faz, E pra pegar o último dia do Mês passado poderíamos usar LAST_DAY, ou poderíamos pegar o primeiro dia do mês atual e tirar um segundo, aí caimos no último dia do mês anterior, tipo :

 SCOTT@xepdb1::CONTAINER=XEPDB1> ed
Gravou file afiedt.buf

  1  select sysdate, last_day(sysdate) last_day, trunc(sysdate, 'month') dia_01, trunc(sysdate, 'month')-(1/86400) ult_dia_mes_ant
  2* from dual
SCOTT@xepdb1::CONTAINER=XEPDB1> /

SYSDATE             LAST_DAY            DIA_01              ULT_DIA_MES_ANT
------------------- ------------------- ------------------- -------------------
21/03/2020 20:26:06 31/03/2020 20:26:06 01/03/2020 00:00:00 29/02/2020 23:59:59

1 linha selecionada.

SCOTT@xepdb1::CONTAINER=XEPDB1>

==> hmmm, o LAST_DAY tem a desvantagem de trazer a HORA atual para o último dia, na verdade queremos ter o último dia do do mês anterior um segundo antes da meia-noite do primeiro dia, aí o BETWEEN traria qquer hora desse dia….
Com isso a consulta ficaria :

SCOTT@xepdb1::CONTAINER=XEPDB1> select CD_AVALIACAO, DH_AVALIACAO
  2   from PAGU_AVALIACAO
  3  WHERE cd_avaliacao = 3459855
  4    AND dh_avaliacao between TRUNC(add_months(SYSDATE, -1), 'MONTH') AND trunc(sysdate, 'month')-(1/86400) ;

CD_AVALIACAO DH_AVALIACAO
------------ -------------------
     3459855 29/02/2020 10:09:08
     3459855 10/02/2020 09:29:40

2 linhas selecionadas.

SCOTT@xepdb1::CONTAINER=XEPDB1>

==> Chegamos no MESMO resultado do exemplo anterior mas SEM aplicar TRUNC ou qquer outra função na coluna DH_AVALIACAO, permitindo assim que um eventual índice nela existe SEJA usado, okdoc ??

[]s

Chiappa

==> OBS : se ficou dúvidas nessa questão de Aritmética de DATAS, como eu faço pra somar ou subtrair dias, horas, minutos ou segundos de um valor DATE no Oracle, dá uma lida no manual de SQL Oracle que tem uma boa explicação a respeito, se ainda assim ficar com alguma dúvida pergunta e eu tento expandir a expolicação, falows ???

[Autor]

Posts