Najlepsze podejście do usunięcia części czasu z datetime w SQL Server

Która metoda zapewnia najlepszą wydajność przy usuwaniu części czasu z pola daty i godziny w SQL Server?

a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

lub

b) select cast(convert(char(11), getdate(), 113) as datetime)

Druga metoda przesyła jeszcze kilka bajtów w jedną stronę, ale może to nie być tak ważne, jak szybkość konwersji.

Oba wydają się być również bardzo szybkie, ale może być różnica prędkości w przypadku setek tysięcy lub więcej rzędów?

Czy jest też możliwe, że istnieją jeszcze lepsze metody na pozbycie się części czasu z datetime w SQL?

Ściśle mówiąc, metoda awymaga najmniejszych zasobów:

a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

Udowodniono, że procesor jest mniej intensywny w tym samym łącznym czasie trwania milion wierszy o jeden za dużo ze zbyt dużą ilością czasu: Najskuteczniejszy sposób w SQL Server, aby uzyskać datę od daty i godziny?

Widziałem podobny test gdzie indziej z podobnymi wynikami.

Wolę DATEADD / DATEDIFF, ponieważ:

• varchar podlega problemom dotyczącym języka / daty
  Przykład: Dlaczego moje wyrażenie CASE jest niedeterministyczne?
• float opiera się na pamięci wewnętrznej
• rozciąga się na ćwiczenie pierwszego dnia miesiąca, jutra itp. poprzez zmianę podstawy „0”

Edycja, październik 2011 r

W przypadku SQL Server 2008+ można CAST na datenp CAST(getdate() AS date). Lub po prostu użyj datetypu danych, więc nie ma czasu na usuwanie.

Edytuj, styczeń 2012 r

Sprawdzony przykład elastyczności: trzeba obliczyć według zaokrąglonej godziny lub daty na serwerze SQL

Edytuj, maj 2012 r

Nie używaj tego w klauzulach WHERE itp. Bez zastanowienia: dodanie funkcji lub CAST do kolumny unieważnia użycie indeksu. Zobacz numer 2 tutaj: http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/

Teraz jest to przykład późniejszych wersji optymalizatora SQL Server poprawnie zarządzających CAST, ale ogólnie będzie to zły pomysł ...

Edycja, wrzesień 2018 r. Dla datetime2

DECLARE @datetime2value datetime2 = '02180912 11:45' --this is deliberately within datetime2, year 0218
DECLARE @datetime2epoch datetime2 = '19000101'

select DATEADD(dd, DATEDIFF(dd, @datetime2epoch, @datetime2value), @datetime2epoch)

W SQL Server 2008 możesz użyć:

CONVERT(DATE, getdate(), 101)

Oczywiście jest to stary wątek, ale w celu uzupełnienia.

Z SQL 2008 możesz używać typu danych DATE, dzięki czemu możesz po prostu:

SELECT CONVERT(DATE,GETDATE())

SELECT CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)

... nie jest dobrym rozwiązaniem, zgodnie z komentarzami poniżej.

Chciałbym usunąć tę odpowiedź, ale zostawię ją tutaj jako kontrprzykład, ponieważ uważam, że wyjaśnienie komentujących, dlaczego nie jest to dobry pomysł, jest nadal przydatne.

W SQL Server 2008 istnieje typ danych DATE (także typ danych TIME).

CAST(GetDate() as DATE)

lub

declare @Dt as DATE = GetDate()

Oto kolejna odpowiedź z innego duplikatu pytania:

SELECT CAST(CAST(getutcdate() - 0.50000004 AS int) AS datetime) 

Ta metoda magicznej liczby działa nieco szybciej niż metoda DATEADD. (Wygląda na to, że ~ 10%)

Czas procesora w kilku rundach miliona rekordów:

DATEADD   MAGIC FLOAT
500       453
453       360
375       375
406       360

Pamiętaj jednak, że te liczby są prawdopodobnie nieistotne, ponieważ są już BARDZO szybkie. O ile nie miałem zestawów rekordów wynoszących 100 000 lub więcej, nie mogłem nawet odczytać Czasu Procesora powyżej zera.

Biorąc pod uwagę fakt, że DateAdd jest przeznaczony do tego celu i jest bardziej niezawodny, powiedziałbym, że użyj DateAdd.

SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME)

Naprawdę lubię:

[date] = CONVERT(VARCHAR(10), GETDATE(), 120)

120Kod format będzie zmusić datę do standardu ISO 8601:

'YYYY-MM-DD' or '2017-01-09'

Bardzo łatwy w użyciu w dplyr ( R) i pandach ( Python)!

STRZEC SIĘ!

Metoda a) ib) NIE zawsze ma taki sam wynik!

select DATEADD(dd, DATEDIFF(dd, 0, '2013-12-31 23:59:59.999'), 0)

Wynik: 2014-01-01 00:00:00.000

select cast(convert(char(11), '2013-12-31 23:59:59.999', 113) as datetime)

Wynik: 2013-12-31 00:00:00.000

(Testowany na MS SQL Server 2005 i 2008 R2)

EDYCJA: Zgodnie z komentarzem Adama, nie może się to zdarzyć, jeśli odczytasz wartość daty z tabeli, ale może się tak zdarzyć, jeśli podasz wartość daty jako literał (przykład: jako parametr procedury składowanej wywoływanej przez ADO.NET).

Najpierw usuń czas na wstawkach / aktualizacjach. Jeśli chodzi o konwersję w locie, nic nie jest w stanie pokonać funkcji zdefiniowanej przez użytkownika pod względem konserwacji:

select date_only(dd)

Implementacja date_onlymoże być dowolna - teraz jest abstrakcyjna, a wywoływanie kodu jest znacznie czystsze.

Zobacz to pytanie:
Jak obciąć datę i godzinę w programie SQL Server?

Cokolwiek robisz, nie używaj metody string . To najgorszy sposób, w jaki możesz to zrobić.

Odpowiedziałem już, ale źle to też tam wyrzucam ... to podobno również dobrze się sprawdza, ale działa to przez wyrzucenie przecinka (który przechowuje czas) z pływaka i zwrócenie tylko całej części (która jest datą)

 CAST(
FLOOR( CAST( GETDATE() AS FLOAT ) )
AS DATETIME
)

drugi raz znalazłem to rozwiązanie ... złapałem ten kod

CAST(round(cast(getdate()as real),0,1) AS datetime)

Ta metoda nie używa funkcji łańcuchowej. Datejest w zasadzie prawdziwym typem danych z cyframi po przecinku, które stanowią ułamek dnia.

myślę, że będzie to szybsze niż wiele.

Dla mnie poniższy kod jest zawsze zwycięzcą:

SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT,GETDATE())));

wybierz CONVERT (char (10), GetDate (), 126)

Myślę, że chodzi Ci o cast(floor(cast(getdate()as float))as datetime)

real ma tylko 32 bity i może stracić trochę informacji

To jest najszybsze cast(cast(getdate()+x-0.5 as int)as datetime)

... choć tylko około 10% szybciej(about 0.49 microseconds CPU vs. 0.58)

To było zalecane i zajmuje teraz ten sam czas w moim teście: DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

W SQL 2008 funkcja SQL CLR jest około 5 razy szybsza niż użycie funkcji SQL, przy 1,35 mikrosekundy w porównaniu z 6,5 mikrosekcjami, co wskazuje na znacznie niższy narzut wywołania funkcji dla funkcji SQL CLR w porównaniu z prostym SQL UDF.

W SQL 2005 funkcja SQL CLR jest 16 razy szybsza, według moich testów, w porównaniu z tą wolną funkcją:

create function dateonly (  @dt datetime )
returns datetime
as
begin
return cast(floor(cast(@dt as float))as int)
end

Jak o select cast(cast my_datetime_field as date) as datetime)? Powoduje to tę samą datę, z czasem ustawionym na 00:00, ale pozwala uniknąć konwersji na tekst, a także pozwala uniknąć jawnego zaokrąglania liczb.

Myślę, że jeśli trzymasz się tego ściśle TSQL, jest to najszybszy sposób na skrócenie czasu:

 select convert(datetime,convert(int,convert(float,[Modified])))

Odkryłem, że ta metoda obcięcia jest o około 5% szybsza niż ta DateAddmetoda. Można to łatwo zmienić, aby zaokrąglić do najbliższego dnia w następujący sposób:

select convert(datetime,ROUND(convert(float,[Modified]),0))

Tutaj utworzyłem funkcję usuwania niektórych części datetime dla SQL Server. Stosowanie:

• Pierwszy parametr to data i godzina, która ma zostać usunięta.
• Drugi parametr to znak:
  • s: zaokrągla do sekund; usuwa milisekundy
  • m: zaokrągla do minut; usuwa sekundy i milisekundy
  • h: zaokrągla do godzin; usuwa minuty, sekundy i milisekundy.
  • d: zaokrągla do dni; usuwa godziny, minuty, sekundy i milisekundy.
• Zwraca nową datę / godzinę

create function dbo.uf_RoundDateTime(@dt as datetime, @part as char) returns datetime as begin if CHARINDEX( @part, 'smhd',0) = 0 return @dt; return cast( Case @part when 's' then convert(varchar(19), @dt, 126) when 'm' then convert(varchar(17), @dt, 126) + '00' when 'h' then convert(varchar(14), @dt, 126) + '00:00' when 'd' then convert(varchar(14), @dt, 112) end as datetime ) end

Na wypadek, gdyby ktoś szukał tutaj wersji Sybase, ponieważ kilka powyższych wersji nie działało

CAST(CONVERT(DATE,GETDATE(),103) AS DATETIME)

• Testowane w I SQL v11 działającym na serwerze Adaptive Server 15.7

Jeśli to możliwe, do takich specjalnych rzeczy lubię korzystać z funkcji CLR.

W tym przypadku:

[Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDateTime DateOnly(SqlDateTime input)
    {
        if (!input.IsNull)
        {
            SqlDateTime dt = new SqlDateTime(input.Value.Year, input.Value.Month, input.Value.Day, 0, 0, 0);

            return dt;
        }
        else
            return SqlDateTime.Null;
    }

Ja osobiście prawie zawsze korzystam z funkcji zdefiniowanych przez użytkownika do tego przez użytkownika, jeśli mamy do czynienia z SQL Server 2005 (lub niższą wersją), należy jednak zauważyć, że istnieją szczególne wady korzystania z UDF, szczególnie jeśli stosuje się je do klauzul WHERE (patrz poniżej i komentarze do tej odpowiedzi w celu uzyskania dalszych szczegółów). Jeśli używasz programu SQL Server 2008 (lub nowszego) - patrz poniżej.

W rzeczywistości dla większości baz danych, które tworzę, dodaję te UDF tuż przy początku, ponieważ wiem, że istnieje 99% szansa, że ​​będę ich potrzebować wcześniej czy później.

Tworzę jeden dla „tylko daty” i „tylko czasu” (chociaż „tylko data” jest zdecydowanie najczęściej używanym z nich).

Oto kilka linków do różnych UDF związanych z datą:

Najważniejsze funkcje daty, godziny i daty i godziny w programie SQL Server
Uzyskaj funkcję tylko daty

Ten ostatni link pokazuje nie mniej niż 3 różne sposoby, aby data była tylko częścią pola daty i godziny, i wymienia niektóre zalety i wady każdego podejścia.

Jeśli używasz UDF, należy zauważyć, że powinieneś unikać używania UDF jako części klauzuli WHERE w zapytaniu, ponieważ to znacznie pogorszy wydajność zapytania. Głównym tego powodem jest to, że użycie UDF w klauzuli WHERE powoduje, że klauzula ta nie podlega wymianie , co oznacza, że ​​SQL Server nie może już używać indeksu z tą klauzulą ​​w celu poprawy szybkości wykonywania zapytania. Odnosząc się do własnego użycia UDF, często użyję kolumny „surowej” daty w klauzuli WHERE, ale zastosuję UDF do kolumny WYBRANE. W ten sposób UDF jest stosowany tylko do filtrowanego zestawu wyników, a nie do każdego wiersza tabeli jako część filtru.

Oczywiście absolutnie najlepszym podejściem do tego jest użycie programu SQL Server 2008 (lub nowszego) i oddzielenie dat i godzin , ponieważ silnik bazy danych SQL Server natywnie dostarcza poszczególne komponenty daty i godziny i może efektywnie sprawdzać je niezależnie bez potrzeby stosowania UDF lub innego mechanizmu do wyodrębnienia części daty lub godziny z typu złożonego typu data-godzina.

Użyłbym:

CAST
(
CAST(YEAR(DATEFIELD) as varchar(4)) + '/' CAST(MM(DATEFIELD) as varchar(2)) + '/' CAST(DD(DATEFIELD) as varchar(2)) as datetime
) 

W ten sposób skutecznie tworzysz nowe pole z pola daty, które już masz.