A peça que o between pregou!

Boas pessoal! Começarei meu primeiro post falando de SQL!
Antes, uma observação: O SGBD onde o problema ocorreu e as queries foram testadas é PostgreSQL 8.3.

Fui procurado para tirar uma dúvida sobre uma query que utilizava between. Como sempre faço, separo as ações da seguinte forma:

O problema: “O intervalo de datas não está funcionando quando coloco 12/06/2009 a 10/07/2009”.

A estrutura:

ID......Data
1 30/06/2009 00:00
2 02/07/2009 09:00
3 10/07/2009 11:00

A primeira suspeita:
O erro mais comum é quando o campo armazena data/hora e comparamos apenas com data, conforme exemplo abaixo:

select id from tmp_post
where dt_post between to_date( '12/06/2009', 'dd/mm/yyyy') and to_date( '10/07/2009', 'dd/mm/yyyy');

O resultado disto seria o retorno de apenas 2 registros (30/06 e 02/07). Por que?
Porque 10/07/2009 11:00 é maior que 10/07/2009 00:00. Simples assim! Por pura falta de atenção, mas acontece.

Maaaas, para ajudar a me deixar mais careca, este problema já estava calçado, então, só restou a "apelação"....
E foi constatado, não é que estavam comparando varchar?
E quando vc usa varchar, "12/06/2009" é maior que "10/07/2009", sendo assim, nosso intervalo estava furado.
Para ilustrar, estava mais ou menos assim:

select * from tmp_post where to_char(dt_post, 'dd/mm/yyyy hh24:mi') between '12/06/2009' and '10/07/2009';

Isto não traz nenhum registro.

Mas porque '12/06/2009' é maior que '10/07/2009'?
Estamos usando estes dois valores como varchar(string);
Primeiro o SGBD "compara" pelo primeiro caracter - ambos têm 1;
Segundo o SGBD "compara" pelo segundo caracter - 2 é maior que 0; Bingo!

Problema detectado, um cascudo no programador e agora vamos resolver!
Basta comparar por datas e não mais por varchar, da seguinte forma:

select * from tmp_post
where dt_post between to_date('12/06/2009', 'dd/mm/yyyy' ) and to_date('10/07/2009', 'dd/mm/yyyy');

Opa, mas com isso eu passo a ter o problema número 1, de retornar apenas dois registros (30/06 e 02/07).
Para trabalhar com isto, existem dois caminhos:
1) Mude a forma como vc está passando o parâmetro, concatenando a última hora com o segundo parâmetro, ficando:

select * from tmp_post
where dt_post between to_date('12/06/2009', 'dd/mm/yyyy' ) and to_date('10/07/2009 23:59:59', 'dd/mm/yyyy hh24:mm:ss');

Retorno: 30/06, 02/07 e 10/07

2) Formate a data que está armazenada, desprezando a hora, mas dependendo da configuração do SGBD, vc precisará transformar em string com a data adequada(com to_char) e depois formata para data, já sem as horas (to_date):

select * from tmp_post
where to_date(to_char(dt_post,'dd/

mm/yyyy'),'dd/mm/yyyy') between to_date('12/06/2009', 'dd/mm/yyyy' ) and to_date('10/07/2009 23:59:59', 'dd/mm/yyyyhh24:mm:ss');

Retorno: 30/06, 02/07 e 10/07

Na opção 1, vc precisa concatenar o valor fixo '23:59:59';
Na opção 2, vc precisa utilizar duas funções do SGBD, to_char e depois to_date;

Como penso que o mais importante quando estiver trabalhando com datas é manter o controle do dado que vc está trabalhando, prefiro a segunda opção. Mesmo utilizando um pouco mais de recurso do SGBD.

Por enquanto é isso.

Abs. e até a próxima!
Ed