Zet SQL server datetime velden om de datum slechts onderdelen te vergelijken, met geïndexeerde lookups

stemmen
28

Ik heb een te doen convert(varchar,datefield,112)op elke datum veld dat ik gebruik in 'tussen' queries in SQL server om ervoor te zorgen dat ik alleen goed voor data en niet te missen elke gebaseerd op het tijdstip deel van datetime velden.

Nu, ik hoor dat de bekeerlingen niet geïndexeerd en dat er betere methoden, in SQL Server 2005, de datum deel van tijd-tags te vergelijken in een query om te bepalen of data in een bereik vallen.

Wat is de optimale, geïndexeerd, de wijze van zoiets als dit te doen:

select * from appointments
where appointmentDate>='08-01-2008' and appointmentDate<'08-15-2008'
De vraag is gesteld op 09/12/2008 om 15:59
bron van user
In andere talen...                            


5 antwoorden

stemmen
65

De beste manier om het tijdsdeel van datetime veld strook gebruikt datediff en DateAdd functies.

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

Dit duurt AdvantEdge van het feit dat SQL Server u datums als twee getallen, een vertegenwoordiger van het aantal dagen sinds de dag "0" - (1 januari 1900), en de tweede die het aantal vertegenwoordigt teken (elke teek is ongeveer 3,33 ms ) sinds middernacht (voor de tijd) *.

de bovenstaande formule móet alleen lezen van het dichtstbijzijnde gehele getal. Er is geen conversie of verwerkingen, dus het is extreem snel.

Om uw vragen te gebruiken een index ... gebruiken deze formule op de input eerste filteren van parameters, of aan de "andere" kant van het isgelijkteken vanaf het moment dat veld tafels datum, zodat de query optimizer hoeft niet de berekening draaien op elke datetime veld in de tabel om te bepalen welke rijen voldoen aan de filter predikaat. Dit maakt uw zoekopdracht argument "SARG-able" (Search argument)

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

liever dan

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

* NOTITIE. Intern het tweede getal (het tijddeel) slaat teken. Ten dage zijn er 24 x 60 x 60 x 300 = 25920000 teken (serendipitously net onder de maximale waarde een 32 bit integer kan bevatten). Echter, hoeft u geen zorgen te maken over wanneer rekenkundig datetime het wijzigen ... Wanneer optellen of aftrekken van de waarden van tijd-tags kunt u de waarde te behandelen als een breuk alsof het was precies gelijk aan het fractionele gedeelte van een dag, alsof de volledige datetime waarde een decimaal getal bestaat uit een gehele getal vertegenwoordigt de datum en het fractionele gedeelte vertegenwoordigt de tijd). d.w.z,

`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`
antwoordde op 09/12/2008 om 16:07
bron van user

stemmen
5

Het omzetten van numerieke Tekenreekswaarden (een soort Boxing) is niet de best presterende methode van het doen van wat u zoekt. Het is niet echt over index-staat, omdat de werkelijke aard kolom datum tijd.

Als u op zoek bent naar de beste manier query voor data, dan is uw voorbeeld is goed, maar je zou willen rekening houden met de 3 ms precisie verschil in MSSQL. Het kan betekenen dat de gegevens van de ene dag kan worden weergegeven in het resultaat van een andere dag.

Deze

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

Mocht dit

select * from appointments where appointmentDate>='08-01-2008' and appointmentDate<='08-14-2008 23:59:59.996'
antwoordde op 09/12/2008 om 16:11
bron van user

stemmen
2

Het is juist - het doen van de conversie zal de conversie voor elke rij bevraagd uit te voeren. Het is beter om de datumkolommen laten zoals data, en geef in je, waar clausules als data:

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

Opmerking: Het verlaten van de tijd betekent om middernacht (00: 00.000), dus je zal alle tijden voor de 08/01, en te allen tijde van 08/15, en alles wat dat is precies 2008/08/16 00:00:00

antwoordde op 09/12/2008 om 16:06
bron van user

stemmen
1

Heeft u een berekende aanhield kolom berekenen van de expressie die u nodig hebt. Als kolommen worden berekend en aanhield, kunnen ze ook worden geïndexeerd.

antwoordde op 09/12/2008 om 16:10
bron van user

stemmen
0

Er is ook de beschreven manier http://www.stillnetstudios.com/comparing-dates-without-times-in-sql-server/

SELECT CAST(FLOOR(CAST( getdate() AS float )) AS datetime)
antwoordde op 28/09/2011 om 13:31
bron van user

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