Início / dba / Perguntas / 27449
Accepted
fa1c0n3r
Asked: 2012-10-24 02:35:22 +0800 CST

Consulta simples com tipo de data requer conversão explícita to_date of date

  • 772

Minha pergunta é, tenho uma tabela que contém uma coluna chamada myDateColumn (por exemplo) que é do tipo data:

Posso confirmar que desc myTablecontém esta linha:

myDateColumn  NOT NULL     DATE

Porém, quando tento selecionar todos os dados que estão entre determinadas datas, por exemplo:

select * from myTable 
where myDateColumn 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

Recebo resultados nulos, não importa o que aconteça, embora tenha certeza de que alguns dados existem dentro desse intervalo de datas específico.

A correção que descobri foi converter explicitamente myDateColumn em uma data:

select myDateColumn from myTable 
where to_date(myDateColumn) 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

que retorna os resultados corretos, mas já é um tipo de data! Por que preciso converter explicitamente uma coluna de data em data antes de fazer a betweencomparação lógica?

O motivo de eu perguntar é porque esse select/ where fará parte de um procedimento armazenado selecionando dados dentro de um intervalo de data específico para ser executado em uma grande tabela particionada por data, com mais de dez milhões de linhas, e se eu puder evitar converter explicitamente cada myDateColumn de cada linha, então eu poderia economizar algum tempo de consulta, em teoria.

Ou existe uma maneira mais correta de executar essa comparação para selecionar dados em um intervalo de datas?

Obrigada.

Atualização: Leigh sugeriu na resposta diagnosticar a tabela por algo inconsistente com sua consulta. O resultado de sua consulta foi:

myDateColumn    TO_DATE(myDateColumn)   TO_CHAR(myDateColumn,'DD-MON-YYHH.MI.SSPM') TO_CHAR(myDateColumn,'YYYY')  
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011

Indicando que o motivo da betweencláusula não estar funcionando, é porque nenhum dos dados cairia no intervalo de consulta, já que o ano era 0011, durante o Império Romano :)

Obrigado rapazes.

oracle oracle-11g-r2

2 respostas

  1. Todas as datas no Oracle têm um componente de hora.

    Tenho certeza que você quer todas as linhas entre o 13º e o 15º incluídas . No entanto, quando você escreve:

    BETWEEN to_date('13-02-2011', 'DD-MM-YYYY') AND TO_DATE('15-02-2011', 'DD-MM-YYYY')

    você seleciona apenas todas as datas até a 15-02-2011meia-noite. Todas as linhas posteriores a esse dia não serão selecionadas.

    O que está acontecendo é que quando você usa TO_DATEem uma data, ela será convertida em VARCHAR2 e depois convertida novamente em uma data. O efeito líquido será um truncamento no formato de data, conforme mostrado:

    SQL> alter session set nls_date_format='dd/mm/yyyy';

Session altered.

SQL> select to_char(to_date(sysdate), 'dd/mm/yyyy hh24:mi:ss') trunc,
  2         to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') not_trunc
  3    from dual;

TRUNC               NOT_TRUNC
------------------- -------------------
23/10/2012 00:00:00 23/10/2012 15:15:32

    Portanto, suas duas cláusulas não são equivalentes. O segundo seleciona todo o dia 15 de fevereiro, enquanto o primeiro seleciona apenas o primeiro segundo do dia.

    Sugiro que você use as seguintes construções ao escrever o intervalo de datas:

    WHERE dat >= to_date('13-02-2011', 'DD-MM-YYYY')
  AND dat < to_date('16-02-2011', 'DD-MM-YYYY')

    Também não use MONou DAYformate em seu código, pois eles dependem do idioma. Não use YY, pois isso pode ser confuso (já ouviu falar do bug Y2k?). Ambos os formatos são adequados para exibir informações para seus usuários, é claro.

    • 3
  2. Best Answer

    Sua crença de que isso não deveria ser necessário está correta com base em suas suposições. Normalmente, isso significa que as suposições devem ser verificadas novamente (a menos que haja corrupção de dados ou um bug). Se você executar o seguinte, deverá obter os mesmos resultados da primeira consulta que a segunda:

    DROP TABLE t1;
CREATE TABLE t1 AS (
   SELECT to_date('01-FEB-2011','DD-MON-YYYY')+level myDateColumn 
   FROM dual CONNECT BY level <=120);

select * from t1 
where myDateColumn 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

select myDateColumn from t1 
where to_date(myDateColumn) 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

    Para verificar algumas das suposições, você pode mostrar os resultados da consulta a seguir?

    SELECT myDateColumn, to_date(myDateColumn)
   , to_char(myDateColumn,'DD-MON-YY HH.MI.SS PM'), to_char(myDateColumn,'YYYY') 
FROM myTable
WHERE to_date(myDateColumn)
BETWEEN to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

    As três primeiras colunas devem mostrar informações idênticas e a última deve verificar se o ano está correto.

    • 1

relate perguntas