Dlaczego data / godzina wyszukiwania zapytania nie pasuje?

select * 
from A 
where posted_date >= '2015-07-27 00:00:00.000' 
  and posted_date  <= '2015-07-27 23:59:59.999'

Ale wynik zawiera rekord, który opublikował dzisiaj: 28-07-28. Mojego serwera bazy danych nie ma w moim kraju. Jaki jest problem ?

Ponieważ używasz datetimetypu danych, musisz zrozumieć, w jaki sposób serwer sql zaokrągla dane daty i godziny.

╔═══════════╦═════╦═════════════════════════════╦═════════════════════════════╦══════════╦═══════════╗
║   Name    ║ sn  ║        Minimum value        ║        Maximum value        ║ Accuracy ║  Storage  ║
╠═══════════╬═════╬═════════════════════════════╬═════════════════════════════╬══════════╬═══════════╣
║ datetime  ║ dt  ║ 1753-01-01 00:00:00.000     ║ 9999-12-31 23:59:59.997     ║ 3.33 ms  ║ 8 bytes   ║
║ datetime2 ║ dt2 ║ 0001-01-01 00:00:00.0000000 ║ 9999-12-31 23:59:59.9999999 ║ 100ns    ║ 6-8 bytes ║
╚═══════════╩═════╩═════════════════════════════╩═════════════════════════════╩══════════╩═══════════╝

Korzystając z poniższej kwerendy, możesz łatwo zobaczyć problem zaokrągleń, jakie robi serwer SQL podczas korzystania z DATETIMEtypu danych.

select  '2015-07-27 00:00:00.000'                       as Original_startDateTime,
        convert(datetime ,'2015-07-27 00:00:00.000')    as startDateTime,
        '2015-07-27 23:59:59.999'                       as Original_endDateTime,
        convert(datetime ,'2015-07-27 23:59:59.999')    as endDateTime,
        '2015-07-27 00:00:00.000'                       as Original_startDateTime2,
        convert(datetime2 ,'2015-07-27 00:00:00.000')   as startDateTime2,  -- default precision is 7
        '2015-07-27 23:59:59.999'                       as Original_endDateTime2,
        convert(datetime2 ,'2015-07-27 23:59:59.999')   as endDateTime2     -- default precision is 7

Kliknij, aby powiększyć

DATETIME2istnieje już od SQL Server 2008, więc zacznij go używać zamiast DATETIME. W Twojej sytuacji możesz użyć datetime2z dokładnością do 3 miejsc po przecinku, np datetime2(3).

Korzyści z używania datetime2:

• Obsługuje do 7 miejsc po przecinku dla komponentu czasu vs datetimewspieranie tylko 3 miejsc po przecinku .. i stąd widać problem zaokrąglania ponieważ domyślnie datetimerund najbliższa .003 secondsze skokiem .000, .003lub .007sekund.
• datetime2jest znacznie bardziej precyzyjny niż datetimei datetime2daje kontrolę DATEi TIMEw przeciwieństwie do datetime.

Odniesienie :

• Najlepszy przewodnik po typach danych datetime
• Program SQL Server DATE / TIME Workbench

Jak kilka innych wspomniało w komentarzach i innych odpowiedziach na twoje pytanie, głównym problemem jest 2015-07-27 23:59:59.999zaokrąglanie 2015-07-28 00:00:00.000przez SQL Server. Zgodnie z dokumentacją dla DATETIME:

Zakres czasu - od 00:00:00 do 23: 59: 59,997

Pamiętaj, że przedział czasowy nigdy nie może być .999. W dalszej części dokumentacji określono reguły zaokrąglania używane przez program SQL Server dla najmniej znaczącej cyfry.

Zauważ, że najmniej znacząca cyfra może mieć tylko jedną z trzech potencjalnych wartości: „0”, „3” lub „7”.

Istnieje kilka rozwiązań / obejść tego problemu, z których można skorzystać.

-- Option 1
SELECT 
    * 
FROM A 
WHERE posted_date >= '2015-07-27 00:00:00.000' 
  AND posted_date <  '2015-07-28 00:00:00.000' --Round up and remove equality

-- Option 2
SELECT 
    * 
FROM A 
WHERE posted_date >= '2015-07-27 00:00:00.000' 
  AND posted_date <=  '2015-07-27 23:59:59.997' --Round down and keep equality

-- Option 3
SELECT 
    * 
FROM A 
WHERE CAST(posted_date AS DATE) = '2015-07-27' -- Use different data type

-- Option 4
SELECT 
    * 
FROM A 
WHERE CONVERT(CHAR(8), DateColumn, 112) = '20150727' -- Cast to string stripping off time

-- Option 5
SELECT 
    * 
FROM A 
WHERE posted_date BETWEEN '2015-07-27 00:00:00.000' 
  AND '2015-07-27 23:59:59.997' --Use between

Spośród pięciu przedstawionych powyżej opcji rozważę opcje 1 i 3 jako jedyne możliwe opcje. Jasno przekazują twoją intencję i nie ulegną zniszczeniu, jeśli zaktualizujesz typy danych. Jeśli korzystasz z programu SQL Server 2008 lub nowszego, uważam, że preferowanym rozwiązaniem powinna być opcja 3. Jest to szczególnie prawdziwe, jeśli możesz zrezygnować z używania DATETIMEtypu DATEdanych na typ danych dla posted_datekolumny.

Jeśli chodzi o opcję 3, bardzo dobre wyjaśnienie niektórych kwestii można znaleźć tutaj: przesyłanie do tej pory jest możliwe do sprzedaży, ale czy to dobry pomysł?

Nie podoba mi się opcja 2 i 5, ponieważ .997ułamki sekund będą kolejną magiczną liczbą, którą ludzie będą chcieli „naprawić”. Z kilku innych powodów, dla których BETWEENnie jest powszechnie stosowany, możesz sprawdzić ten post .

Nie podoba mi się opcja 4, ponieważ konwersja typów danych na ciąg dla celów porównawczych jest dla mnie brudna. Bardziej jakościowym powodem, aby tego uniknąć w SQL Server, jest to, że ma on wpływ na możliwości sprzedaży, czyli nie można wykonać wyszukiwania indeksu, co często skutkuje gorszą wydajnością.

Aby uzyskać więcej informacji na właściwej drodze i niewłaściwy sposób do tej pory uchwyt Zakres zapytaniami kasy ten post przez Aaron Bertrand .

W rozstaniu będziesz mógł zachować oryginalne zapytanie i zachowa się tak, jak chcesz, jeśli zmienisz posted_datekolumnę z a DATETIMEna DATETIME2(3). Pozwoliłoby to zaoszczędzić miejsce na serwerze, dać większą dokładność przy tej samej precyzji, być bardziej zgodnym ze standardami / przenośnym oraz pozwolić na łatwą regulację dokładności / precyzji, jeśli potrzeby zmienią się w przyszłości. Jest to jednak opcja tylko w przypadku korzystania z programu SQL Server 2008 lub nowszego.

Ciekawostką jest 1/300to, że druga dokładność DATETIMEwydaje się być wstrzymywana przez UNIX dla tej odpowiedzi StackOverflow . Sybase, która ma wspólne dziedzictwo, ma podobną 1/300drugą dokładność w swoich typach danych DATETIMEiTIME typach danych, ale ich najmniej znaczące cyfry różnią się dotykiem „0”, „3” i „6”. Moim zdaniem 1/300dokładność sekundowa i / lub 3,33 ms jest niefortunną decyzją architektoniczną, ponieważ 4-bajtowy blok w DATETIMEtypie danych programu SQL Server mógł z łatwością obsługiwać dokładność 1 ms.

Implikowana konwersja

Przypuszczam, że typem danych data_przesłania jest Datetime. Jednak nie ma znaczenia, czy typ po drugiej stronie to Datetime, Datetime2, czy tylko Czas, ponieważ ciąg (Varchar) zostanie domyślnie przekonwertowany na Datetime.

W przypadku zadeklarowanej daty_daty jako Datetime2 (lub Time) posted_date <= '2015-07-27 23:59:59.99999'klauzula where zawodzi, ponieważ altough 23:59:59.99999jest prawidłową wartością Datetime2, nie jest to poprawna wartość Datetime:

 Conversion failed when converting date and/or time from character string.

Zakres czasu dla Datetime

Zakres czasu Datetime wynosi od 00:00:00 do 23: 59: 59.997. Dlatego 23: 59: 59.999 jest poza zakresem i musi być zaokrąglany w górę lub w dół do najbliższej wartości.

Precyzja

Poza tym wartości Datetime są zaokrąglane o przyrosty 0,000, 0,003 lub 0,007 sekundy. (tj. 000, 003, 007, 010, 013, 017, 020, ..., 997)

Nie dzieje się tak w przypadku wartości z 2015-07-27 23:59:59.999tego zakresu: 2015-07-27 23:59:59.997i 2015-07-28 0:00:00.000.

Ten zakres odpowiada najbliższym opcjom poprzedzającym i następującym po nich, oba kończące się na .000, .003 lub .007.

Zaokrąglanie w górę czy w dół ?

Ponieważ jest bliżej 2015-07-28 0:00:00.000(+1) w stosunku -2 niż 2015-07-27 23:59:59.997, łańcuch jest zaokrąglona i staje się tę wartość Datetime: 2015-07-28 0:00:00.000.

Przy górnym limicie, takim jak 2015-07-27 23:59:59.998(lub .995, .996, .997, .998), byłby zaokrąglony w dół, 2015-07-27 23:59:59.997a zapytanie działałoby zgodnie z oczekiwaniami. Nie byłoby to jednak rozwiązanie, a jedynie szczęśliwa wartość.

Typy Datetime2 lub Time

Datetime2 i czasowe zakresy czasowe są 00:00:00.0000000przez 23:59:59.9999999z dokładnością do 100 ns (ostatnia cyfra po połączeniu z precyzją 7 cyfr).

Jednak zakres Datetime (3) nie jest podobny do zakresu Datetime:

• Datetime 0:0:00.000do23:59:59.997
• Datetime2 0:0:00.000000000do23:59:59.999

Rozwiązanie

Na koniec bezpieczniej jest szukać dat poniżej następnego dnia niż dat poniżej lub równych temu, co uważasz za ostatni fragment pory dnia. Wynika to głównie z tego, że wiesz, że następny dzień zawsze zaczyna się od 0: 00: 00.000, ale różne typy danych mogą nie mieć tej samej godziny na koniec dnia:

Datetime `0:0:00.000` to `23:59:59.997`
Datetime2 `0:0:00.000000000` to `23:59:59.999-999-900`
Time2 `0:0:00.000000000` to `23:59:59.999-999-900`

• < 2015-07-28 0:00:00.000daje dokładne wyniki i jest najlepszą opcją
• <= 2015-07-27 23:59:59.xxx może zwrócić nieoczekiwane wartości, jeśli nie zostaną zaokrąglone w górę do tego, co Twoim zdaniem powinno być.
• Należy unikać konwersji na datę i używania funkcji, ponieważ ogranicza to użycie indeksów

Moglibyśmy pomyśleć, że zmiana [data_postu] na Datetime2 i jego większa precyzja może rozwiązać ten problem, ale to nie pomoże, ponieważ ciąg znaków jest nadal konwertowany na Datetime. Jeśli jednak zostanie dodana obsada cast(2015-07-27 23:59:59.999' as datetime2), działa to dobrze

Przesyłaj i konwertuj

Cast może konwertować wartość z maksymalnie 3 cyframi na Datetime lub z maksymalnie 9 cyfr na Datetime2 lub Time i zaokrąglać ją z właściwą dokładnością.

Należy zauważyć, że Cast of Datetime2 i Time2 może dawać różne wyniki:

• select cast('20150101 23:59:59.999999999' as datetime2(7)) jest zaokrąglany w górę 2015-05-03 00: 00: 00.0000000 (dla wartości większej niż 999999949)
• select cast('23:59:59.999999999' as time(7)) => 23: 59: 59,9999999

To naprawia problem związany z datą z przyrostem 0, 3 i 7, chociaż zawsze lepiej jest szukać dat przed pierwszą nano sekundą następnego dnia (zawsze 0: 00: 00.000).

Źródło MSDN: datetime (Transact-SQL)

Zaokrągla

 select cast('2015-07-27 23:59:59.999' as datetime) 
 returns 2015-07-28 00:00:00.000

.998, .997, .996, .995 wszystkie obsadzone / okrągłe do .997

Powinien użyć

select * 
from A 
where posted_date >= '2015-07-27 00:00:00.000' 
  and posted_date <  '2015-07-28 00:00:00.000'

lub

where cast(posted_date as date) = '2015-07-27'

Zobacz dokładność w tym linku
Zawsze zgłaszane jako .000, .003, .007

select * from A where date(posted_date) = '2015-07-27'