Converter campos de data e hora do SQL Server para comparar única peças de data, com pesquisas indexadas

votos
28

Eu tenho feito um convert(varchar,datefield,112)em cada campo de data que eu estou usando no 'entre' consultas no servidor SQL para garantir que eu só estou respondendo por datas e não faltando qualquer base por parte tempo de campos de data e hora.

Agora, eu estou ouvindo que os convertidos não são intercambiáveis ​​e que existem melhores métodos, no SQL Server 2005, para comparar a parte de data datetimes em uma consulta para determinar se as datas caem em um intervalo.

Qual é o ideal, indexável, método de fazer algo como isto:

select * from appointments
where appointmentDate>='08-01-2008' and appointmentDate<'08-15-2008'
Publicado 09/12/2008 em 15:59
fonte usuário
Em outras línguas...                            


5 respostas

votos
65

A melhor maneira de retirar a parte de tempo de um campo de data e hora é usando datediff e funções DATEADD.

   DateAdd(day, datediff(day,0, MydateValue), 0)

Isso leva AdvantEdge do fato de que o SQL Server armazena data como dois inteiros, um representando o número de dias desde o primeiro dia "0" - (1 jan 1900), ea segunda, que representa o número de carrapatos (cada um carrapato é de cerca de 3,33 ms ) desde meia-noite (para a época) *.

a fórmula acima simplesmente tem que ler apenas o primeiro inteiro. Não há conversão ou de transformação, por isso é extremamente rápido.

Para fazer suas consultas usar um índice ... usar esta fórmula na entrada filtragem parâmetros primeiro, ou no "outro" lado do sinal de igual do campo de data e hora tabelas, de modo que o otimizador de consulta não tem que executar o cálculo em todos os campos de data e hora na tabela para determinar quais linhas satisfazer o predicado de filtro. Isso faz com que a sua pesquisa argumento "SARG-able" (Search argumento)

Where MyDateTimeColumn > DateAdd(day, 
      datediff(day,0, @MydateParameter), 0)    -- SARG-able

ao invés de

Where DateAdd(day, datediff(day,0, 
      MyDateTimeColumn ), 0) > @MydateParameter -- Not SARG-able

* NOTA. Internamente, o segundo número inteiro (a parte da hora) armazena carrapatos. Num dia existem 24 x 60 x 60 x 300 = 25,920,000 carrapatos (por acaso apenas abaixo do valor máximo um número inteiro de 32 bit pode conter). No entanto, você não precisa se preocupar com isso quando aritmeticamente modificando uma data e hora ... Ao adicionar ou subtrair valores de datetimes você pode tratar o valor como uma fração como se fosse exatamente igual à parte fracionária de um dia, como se o valor completo de data e hora era um número de ponto flutuante que consiste de uma porção inteiro que representa a data e a porção fraccionada que representa o tempo). isto é,

`Declare @Dt DateTime  Set @Dt = getdate()  
 Set @Dt = @Dt + 1.0/24  -- Adds one hour  
 Select @Dt  
 Set @Dt = @Dt - .25 -- Moves back 6 hours  
 Select @Dt`
Respondeu 09/12/2008 em 16:07
fonte usuário

votos
5

Convertendo tipos numéricos para valores de cadeia (um tipo de boxe) não é o melhor método de executar de fazer o que você está procurando. A sua não é realmente sobre índice de poder, porque o tipo de coluna real é tempo de data.

Se você está procurando a melhor consulta caminho para datas, então o seu exemplo é certo, mas você pode querer ter em conta a diferença de 3 ms precisão em MSSQL. Pode significar que os registros desde o primeiro dia podem aparecer no resultado de um outro dia.

este

select * from appointments where appointmentDate>='08-01-2008' and appointmentDate<'08-15-2008'

Deve ser este

select * from appointments where appointmentDate>='08-01-2008' and appointmentDate<='08-14-2008 23:59:59.996'
Respondeu 09/12/2008 em 16:11
fonte usuário

votos
2

É correta - fazendo a conversão irá executar a conversão para cada linha consultado. É melhor deixar as colunas de data como datas, e passar em suas cláusulas WHERE como datas:

select * from appointments where appointmentdate between 
'08/01/2008' AND '08/16/2008'

Nota: Deixando de fora o tempo significa a meia-noite (00: 00.000), assim que você vai incluir todos os momentos para 08/01, e todas as vezes a partir de 08/15, e tudo o que é exatamente 2008/08/16 00:00:00

Respondeu 09/12/2008 em 16:06
fonte usuário

votos
1

Tenha um computada persistente coluna calcular a expressão que você precisa. Se as colunas são calculadas e persistiram, eles também podem ser indexados.

Respondeu 09/12/2008 em 16:10
fonte usuário

votos
0

Há também a maneira descrita no http://www.stillnetstudios.com/comparing-dates-without-times-in-sql-server/

SELECT CAST(FLOOR(CAST( getdate() AS float )) AS datetime)
Respondeu 28/09/2011 em 13:31
fonte usuário

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more