Convertire i campi datetime di SQL Server per confrontare solo parti della data, con le ricerche indicizzate

voti
28

Ho fatto una convert(varchar,datefield,112)su ogni campo di data che sto usando in 'tra' le query in SQL Server per assicurarsi che sto solo la contabilità per le date e non perdere alcuna sulla base del part-time dei campi datetime.

Ora, ho sentito che i convertiti non sono indicizzabili e che ci sono metodi migliori, in SQL Server 2005, per confrontare la parte relativa alla data di datetimes in una query per determinare se le date cadono in un intervallo.

Qual è l'ottimale, indicizzabili, metodo di fare qualcosa di simile a questo:

select * from appointments
where appointmentDate>='08-01-2008' and appointmentDate<'08-15-2008'
È pubblicato 09/12/2008 alle 15:59
fonte dall'utente
In altre lingue...                            


5 risposte

voti
65

Il modo migliore per mettere a nudo la parte di tempo di un campo datetime utilizza datediff e funzioni DATEADD.

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

Questo richiede AdvantEdge del fatto che i negozi di SQL Server date come due numeri interi, uno che rappresenta il numero di giorni dal primo giorno "0" - (1 gennaio 1900), e la seconda, che rappresenta il numero di zecche (ogni tick è di circa 3.33 ms ) a partire dalla mezzanotte (per l'epoca) *.

la formula precedente semplicemente deve leggere solo il primo intero. Non v'è alcuna conversione o la trasformazione richiesta, quindi è estremamente veloce.

Per rendere le vostre query utilizzano un indice ... usare questa formula in ingresso il filtraggio dei parametri prima, o in "altro" lato del segno uguale dal campo data e ora le tabelle, in modo che l'ottimizzatore di query non deve eseguire il calcolo su ogni campo datetime nella tabella per determinare quali righe soddisfano il predicato di filtro. Questo rende la ricerca argomento "SARG-grado" (Cerca argomento)

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

piuttosto che

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

* NOTA. Internamente, il secondo intero (la parte di tempo) esercizi zecche. In un giorno ci sono 24 x 60 x 60 x 300 = 25,920,000 zecche (serendipitously appena sotto il valore massimo un intero a 32 bit può contenere). Tuttavia, non è necessario preoccuparsi di questo quando aritmeticamente la modifica di un datetime ... Quando aggiungendo o sottraendo i valori da datetimes si può considerare il valore come una frazione come se fosse esattamente uguale alla parte frazionaria di un giorno, come se la completo valore datetime è un numero decimale costituito da una porzione intero che rappresenta la data e la parte frazionaria rappresenta il tempo). vale a dire,

`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`
Risposto il 09/12/2008 a 16:07
fonte dall'utente

voti
5

Conversione tipi numerici a valori stringa (un tipo di pugilato) non è il metodo migliore esecuzione di fare quello che state cercando. La sua non è realmente circa index-in grado, in quanto il tipo di colonna effettivo è data ora.

Se state cercando la migliore interrogazione strada per le date, allora il vostro esempio è giusto, ma si consiglia di prendere in considerazione la differenza di 3 ms precisione in MSSQL. Può significare che i record di una giornata si può mostrare in seguito un altro giorno.

Questo

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

Dovrebbe essere questo

select * from appointments where appointmentDate>='08-01-2008' and appointmentDate<='08-14-2008 23:59:59.996'
Risposto il 09/12/2008 a 16:11
fonte dall'utente

voti
2

E 'corretto - facendo la conversione eseguirà la conversione per ogni riga interrogato. E 'meglio lasciare le colonne di data come le date, e passare le vostre clausole where come date:

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

Nota: Lasciare fuori dal tempo significa la mezzanotte (00: 00.000), in modo da includere tutti i tempi per 08/01, e tutti i tempi da 08/15, e tutto ciò che è esattamente 2008/08/16 00:00:00

Risposto il 09/12/2008 a 16:06
fonte dall'utente

voti
1

Hanno una calcolata persistente colonna di calcolare l'espressione è necessario. Se le colonne vengono calcolate e persistenti, possono anche essere indicizzati.

Risposto il 09/12/2008 a 16:10
fonte dall'utente

voti
0

V'è anche il modo descritto http://www.stillnetstudios.com/comparing-dates-without-times-in-sql-server/

SELECT CAST(FLOOR(CAST( getdate() AS float )) AS datetime)
Risposto il 28/09/2011 a 13:31
fonte dall'utente

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