Menu de navegação

[ElizaParticipante]

Boa tarde,

Tenho  o campo data de faturamento na tabela NOTA, formato  data timestamp.

Parâmetros:  data inicial e data final de faturamento   dd/mm/yyyy  (sem horas)

Select * from NOTA where datafatur  >= :datainicial  and datafatur <= :datafinal

 

Ex:  se eu quiser trazer os registros de um dia

Select * from NOTA where datafatur  >= ”01/03/2021′   and datafatur <= ”01/03/2021′

Se eu não colocar as horas, ele não traz os registros.

 

[José Laurindo ChiappaModerador]

Blz ? Então, primeiro de tudo no SGBD Oracle vc NÃO TEM um datatype que contenha só datas, TANTO o DATE QUANTO o TIMESTAMP ou o TIMESTAMP WITH TIME ZONE na verdade SEMPRE contém hora/minuto/segundo (E no caso de timestamp, fração de segundo) – se na hora do INSERT não for especificado valores para hora/min/segundo (e fração, no caso do timestamp) o Oracle vai gravar zero MAS o espaço pra esses dados SEMPRE vai estar presente….
SEGUNDO, plz EXPLICA melhor o que é esses “parâmetros”, e como vc os passa pra query : por exemplo, se esses “parâmetros” na verdade forem BIND VARIABLEs que vc cria na sua app ou na sua tool de front-end, vc TEM que as converter pro datatype correto : usar simplesmente como string , confiando na conversão implícita, é uma PÉSSIMA IDÉIA, vc VAI cedo ou tarde obter conversões errôneas…..

Isto posto, é simples : se vc quer comparar apenas a porção dd/mm/yyyy da coluna, OU vc usa as funções de truncagem/arredondamento de valores (como TRUNC) OU, caso a tua BIND VARIABLE seja do tipo string, vc CONVERTE pra timestamp desprezando a porção hh/mm/ss/fração… Meu exemplo :

=> primeiro, ajusto o Formato de Exibição na minha tool de front-end, que é o sqlplus :
scott@DESENV:SQL>alter session set NLS_TIMESTAMP_FORMAT=’dd/mm/yyyy hh24:mi:ss.FF’;

Sessão alterada.

=> crio a tabela e insiro alguns dados :

scott@DESENV:SQL>create table NOTAS(NUM_NOTA number, DT_FATURA timestamp);

Tabela criada.

scott@DESENV:SQL>insert into NOTAS values(1, systimestamp-1);

1 linha criada.

scott@DESENV:SQL>insert into NOTAS values(2, systimestamp);

1 linha criada.

scott@DESENV:SQL>insert into NOTAS values(3, systimestamp);

1 linha criada.

scott@DESENV:SQL>select * from notas;

NUM_NOTA DT_FATURA

---

     1 25/04/2021 10:43:20,000000
     2 26/04/2021 10:43:37,583000
     3 26/04/2021 10:44:21,114000

=> Ok : no MEU caso, a query vai usar como “argumentos” bind variables, que no caso do sqlplus são do tipo string :

scott@DESENV:SQL>variable V_DT_INI varchar2(10);
scott@DESENV:SQL>variable V_DT_FIM varchar2(10);
scott@DESENV:SQL>exec :V_DT_INI := ’26/04/2021′;

Procedimento PL/SQL concluído com sucesso.

scott@DESENV:SQL>exec :V_DT_FIM := ’26/04/2021′;

Procedimento PL/SQL concluído com sucesso.

scott@DESENV:SQL>print v_dt_ini

V_DT_INI

26/04/2021

scott@DESENV:SQL>print v_dt_fim

V_DT_FIM

26/04/2021

=> Aqui é que vem o pulo do gato : NÃO SÓ eu farei a conversão Explícita (usar strings diretamente é SEMPRE arriscado) mas eu TAMBÉM indicarei que ele deve considerar de 0 hora:0minuto:0segundos.0fração até 23h:59m:59s.99999 fração :

scott@DESENV:SQL>ed
Gravou file afiedt.buf

1 select * from NOTAS where dt_fatura
2 between TO_TIMESTAMP(:V_DT_INI || ‘ 00:00:00.000000’, ‘dd/mm/yyyy hh24:mi:ss.FF’)
3* and TO_TIMESTAMP(:V_DT_FIM || ‘ 23:59:59.999999’, ‘dd/mm/yyyy hh24:mi:ss.FF’)
scott@DESENV:SQL>/

NUM_NOTA DT_FATURA

---

     2 26/04/2021 10:43:37.583000
     3 26/04/2021 10:44:21.114000

=> vou passar um outro dia :

scott@DESENV:SQL>exec :V_DT_INI := ’25/04/2021′;

Procedimento PL/SQL concluído com sucesso.

scott@DESENV:SQL>print v_dt_ini

V_DT_INI

25/04/2021

=> vai funcionar perfeitamente, tanto dia inicial quanto o final vai desconsiderar hh/mm/ss.fração, vai pegar todos :

scott@DESENV:SQL>l
1 select * from NOTAS where dt_fatura
2 between TO_TIMESTAMP(:V_DT_INI || ‘ 00:00:00.000000’, ‘dd/mm/yyyy hh24:mi:ss.FF’)
3* and TO_TIMESTAMP(:V_DT_FIM || ‘ 23:59:59.999999’, ‘dd/mm/yyyy hh24:mi:ss.FF’)
scott@DESENV:SQL>/

NUM_NOTA DT_FATURA

---

     1 25/04/2021 10:43:20.000000
     2 26/04/2021 10:43:37.583000
     3 26/04/2021 10:44:21.114000

scott@DESENV:SQL>

==> Ok ??

Abraços,

Chiappa

[ElizaParticipante]

Bingo!!

De todas as soluções que me passaram,  só a sua funcionou.

Muito obrigada pelas dicas!

 

[ElizaParticipante]

Só uma dúvida…

Tenho 3 bases de dados.   Em uma deu certo, na outra dá erro:

ORA-01850: hora deve ser um valor entre 0 e 23

As base são cópias.  Não é estranho ?

 

 

[José Laurindo ChiappaModerador]

Tá : vc FEZ a conversão da string pra timestamp ** COM A MÀSCARA EXPLÍCITA ** no TO_TIMESTAMP, que nem eu fiz ??? Pois essa mensagem é TÍPICA de acontecer quando vc não usa a máscara explícita e completa….

E DETALHE, peça um DESC na tabela de todos os databases, veja lá se por engano nalgum dos databases a coluna foi criada com datatype diferente de TIMESTAMP(6) – de repente foi criado como timestamp(0), digamos…

 

E mostra DIREITINHO o comando que vc está fazendo , de preferência no sqlplus, que por ser em modo texto fica mais fácil de copiar e colar a tela aqui numa resposta, blz ???

[ElizaParticipante]

Foi criado um campo Date na tabela mas o nosso fornecedor terceiro, grava data e hora (00:00:00).

Então, na tabela BASEVDAS:

campo ad_DTEXP = Date

 

Minha aplicação, onde  :DTEXPINI e  :DTEXPFIN    são tipo Date.

 

– 1º teste   –  erro de ORA-01850

SELECT * FROM BASEVDAS BAS
WHERE
BAS.AD_DTEXP between TO_TIMESTAMP(:DTEXPINI || ’00:00:00.000000′, ‘dd/mm/yyyy hh24:mi:ss.FF’)
AND  TO_TIMESTAMP(:DTEXPFIN || ’23:59:59.999999′, ‘dd/mm/yyyy hh24:mi:ss.FF’)

 

– 2º teste  –  não traz nenhuma informação

SELECT * FROM BASEVDAS BAS

WHERE
BAS.AD_DTEXP between TO_TIMESTAMP(:DTEXPINI, ‘dd/mm/yyyy ‘)
AND TO_TIMESTAMP(:DTEXPFIN, ‘dd/mm/yyyy ‘)

 

– 3º teste  –  não traz nenhuma informação

SELECT * FROM BASEVDAS BAS

WHERE
BAS.AD_DTEXP between  :DTEXPINI    AND  :DTEXPFIN

 

 

[ElizaParticipante]

Um detalhe:

Os parâmetros   :DTEXPINI e  :DTEXPFIN    . podem ser Texto, Data, Número Inteiro, Número Decimal, Texto.

Se for Texto, não é possível formatar como data (99/99/9999)  por isso tive que optar pelo Data.

 

Porém, com o tipo  Data,  ocorre erro no select abaixo:

SELECT * FROM BASEVDAS BAS
WHERE
BAS.AD_DTEXP between TO_TIMESTAMP(:DTEXPINI || ’00:00:00.000000′, ‘dd/mm/yyyy hh24:mi:ss.FF’)
AND  TO_TIMESTAMP(:DTEXPFIN || ’23:59:59.999999′, ‘dd/mm/yyyy hh24:mi:ss.FF’)

 

Com o tipo Texto funciona o select mas não é possível formatar o campo (99/99/9999)

 

 

 

 

 

[José Laurindo ChiappaModerador]

Primeira coisa, Please , colega : não nos quebre a perna…. Vc inicialmente tinha dito que a coluna na tabela era TIMESTAMP, agora vc diz que “campo ad_DTEXP = Date” ???? DATE é uma coisa, TIMESTAMP é outra !!!! São COISAS COMPLETAMENTE DIFERENTES!!!!! Se vc não usar os datatypes CORRETOS, vc Só Pode receber erros na cara, os mais aparentemente loucos….. E isso não é NADA ESTRANHO, se vc violar a regra de utilização vc só pode obter resultados não-ótimos….

Muito bem : SUPONDO que o datatype correto na tabela é DATE então (sendo que, como eu Disse, o datatype DATE no Oracle ** sempre ** inclui um porção hora/minuto/segundo, que pode ate estar zerada , mas SEMPRE vai estar presente), é o seguinte : tem sim como vc manipular uma variável DATE pra vc indicar horas/minutos/segundos, inclusive via aritmética de datas (em https://www.profissionaloracle.com.br/forums/topic/diferenca-de-data-com-horas-e-minutos/ inclusive eu mostro como), MAS a minha recomendação no seu caso é : pra ficar mais simples, eu recomendo que na sua app vc transforme as variáveis que quer usar como argumentos na query em STRINGs, e STRING no formato DD/MM/YYYY – com certeza, seja qual for a sua app, vc DEVE ter comando pra isso….
Uma vez obtidas STRINGs (NO FORMATO DD/MM/YYYY) com a data de início e data de fim, a tua consulta fica simples…

=> primeiro, vou criar as duas variáveis string NO FORMATO CORRETO :

scott@DESENV:SQL>variable DTEXPINI varchar2(10);
scott@DESENV:SQL>exec :DTEXPINI := ’01/01/2021′;

Procedimento PL/SQL concluído com sucesso.

scott@DESENV:SQL>print DTEXPINI

DTEXPINI

01/01/2021

scott@DESENV:SQL>variable DTEXPFIN varchar2(10);
scott@DESENV:SQL>exec :DTEXPFIN := ’31/12/2021′;

Procedimento PL/SQL concluído com sucesso.

scott@DESENV:SQL>print DTEXPFIN

DTEXPFIN

31/12/2021

==> Ok, só pra eu poder testar, vou criar a tabela e inserir uns dados :

scott@DESENV:SQL>create table BASEVDAS (AD_DTEXP date) ;

Tabela criada.

scott@DESENV:SQL>insert into BASEVDAS values (sysdate);

1 linha criada.

scott@DESENV:SQL>insert into BASEVDAS values (sysdate-3);

1 linha criada.

=> OK, vamos à query, onde concateno na string dd/mm/yyyy o componente hora E então converto o valor todo para DATE :

scott@DESENV:SQL>SELECT * FROM BASEVDAS BAS
2 WHERE BAS.AD_DTEXP between TO_DATE(:DTEXPINI || ‘ 00:00:00’, ‘dd/mm/yyyy hh24:mi:ss’)
3 AND TO_DATE(:DTEXPFIN || ‘ 23:59:59’, ‘dd/mm/yyyy hh24:mi:ss’)
4 ;

AD_DTEXP

27/04/2021 16:47:12
24/04/2021 16:47:25

scott@DESENV:SQL>

==> Okdoc ?? Não tem como errar se fazer direito… Só Atenção, que há um ESPAÇO entre a string com dd/mm/yyyy E a string com hh:mi:ss, certo ??

Abraços,

Chiappa

[ElizaParticipante]

Vlw!!

Consegui fazer convertendo a data para string

SELECT * FROM BASEVDAS BAS
WHERE

BAS.AD_DTEXPGDL >=

TO_TIMESTAMP((TO_CHAR(:DTEXPINI,’DD’) ||’/’ || TO_CHAR(:DTEXPINI,’MM’)||’/’ || TO_CHAR(:DTEXPINI,’YYYY’)) || ‘ 00:00:00.000000’, ‘dd/mm/yyyy hh24:mi:ss.FF’)
AND

BAS.AD_DTEXPGDL <=
TO_TIMESTAMP((TO_CHAR(:DTEXPFIN,’DD’) ||’/’ || TO_CHAR(:DTEXPFIN,’MM’)||’/’ || TO_CHAR(:DTEXPFIN,’YYYY’)) || ‘ 00:00:00.000000’, ‘dd/mm/yyyy hh24:mi:ss.FF’)

 

 

 

[José Laurindo ChiappaModerador]

Sim, se a tua tool realmente não permite gerar variável string (é meio estranho isso, gerar string é funcionalidade basicamente padrão seja em que tool de programação for, mas tá) ok – converter o valor date gerado pela app para string é uma possibilidade…. Única coisa, REPITO : o datatype DATE é uma coisa, e o datatype TIMESTAMP é COMPLETAMENTE OUTRA COISA… Assim sendo, SE a coluna na tabela é DATE, vc deveria ter usado TO_DATE, não TO_TIMESTAMP…. Outra coisa, é ABSURDO , complexo e sem sentido ficar convertendo CADA pedacinho da data, que nem vc fez ….
A versão corrigida/simplificada ficaria Tipo assim :

SELECT * FROM BASEVDAS BAS
 WHERE BAS.AD_DTEXPGDL
    BETWEEN TO_DATE(TO_CHAR(:DTEXPINI,'DD/MM/YYYY') || ' 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
        AND TO_DATE(TO_CHAR(:DTEXPFIM,'DD/MM/YYYY') || ' 23:59:59', 'dd/mm/yyyy hh24:mi:ss')
;

Muito mais curto e inteligível….

[ElizaParticipante]

Eu já tinha tentado dessa forma, mas ocorre erro no sistema  (erro de mês válido).

A única maneira que passou foi desmembrando.

O sistema apresenta uma tela para criação de relatórios, onde colocamos o script e parâmetros.  Testo primeiro no sqldeveloper e depois copio o script pra aplicação.  Mas quando se trata de data,  tenho que ficar ajustando.

Mas vc sempre me ajuda bastante.   Eu só tenho a agradecer.

 

 

 

 

[José Laurindo ChiappaModerador]

Bom, sendo código SQL padrão Oracle não faz sentido dar erro SE vc passar a máscara de formatação completa no TO_CHAR, que nem eu fiz, mas tá – se funcionou pra vc tá bom…

Abraços,

Chiappa

[ElizaParticipante]

Sim,  fizeram uma aplicação no sistema pra colocar o script.  Isso é muito legal, porque funcionando no sqldeveloper…  é só control C , control V.

Mas…   alguns comandos não funcionam (quando é data).

 

 

 

 

[Autor]

Posts