Jak mogę obciąć datę / godzinę w SQL Server?


280

Jaki jest najlepszy sposób obcięcia wartości daty i godziny (w celu usunięcia godzin, minut i sekund) w SQL Server 2008?

Na przykład:

declare @SomeDate datetime = '2009-05-28 16:30:22'
select trunc_date(@SomeDate)

-----------------------
2009-05-28 00:00:00.000

Odpowiedzi:


494

To nadal często zbiera dodatkowe głosy, nawet kilka lat później, dlatego muszę je zaktualizować do nowoczesnych wersji Sql Server. W przypadku Sql Server 2008 i nowszych jest to proste:

cast(getDate() As Date)

Pamiętaj, że trzy ostatnie akapity w dolnej części nadal obowiązują i często musisz cofnąć się o krok i znaleźć sposób na uniknięcie obsady.

Ale są też inne sposoby na osiągnięcie tego. Oto najczęstsze.

Prawidłowy sposób (nowość od Sql Server 2008):

cast(getdate() As Date)

Prawidłowy sposób (stary):

dateadd(dd, datediff(dd,0, getDate()), 0)

Jest to teraz starsze, ale nadal warto o tym wiedzieć, ponieważ można je łatwo dostosować do innych punktów czasowych, takich jak pierwsza chwila miesiąca, minuty, godziny lub roku.

Ten prawidłowy sposób wykorzystuje udokumentowane funkcje, które są częścią standardu ansi i mają gwarancję działania, ale może być nieco wolniejsze. Działa poprzez sprawdzenie, ile dni jest od dnia 0 do dnia bieżącego, i dodanie tych dni z powrotem do dnia 0. Będzie działać bez względu na to, jak przechowywana jest data i data, bez względu na ustawienia regionalne.

Szybki sposób:

cast(floor(cast(getdate() as float)) as datetime)

Działa to, ponieważ kolumny datetime są przechowywane jako 8-bajtowe wartości binarne. Rzuć je, aby unosić się, wyrównaj podłogę, aby usunąć ułamek, a część wartości czasu zniknie, gdy rzucisz je z powrotem na datę i godzinę. Wszystko przesuwa się trochę bez skomplikowanej logiki i jest bardzo szybkie.

Pamiętaj, że zależy to od szczegółów implementacyjnych, które Microsoft może zmienić w dowolnym momencie, nawet w przypadku automatycznej aktualizacji usług. Nie jest też zbyt przenośny. W praktyce jest mało prawdopodobne, aby ta implementacja uległa zmianie w najbliższym czasie, ale nadal ważne jest, aby zdawać sobie sprawę z niebezpieczeństwa, jeśli zdecydujesz się z niej skorzystać. A teraz, gdy mamy opcję przesyłania jako daty, rzadko jest to konieczne.

Zły kierunek:

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

Niepoprawny sposób działa przez konwersję na ciąg, obcinanie ciągu i konwertowanie z powrotem do daty i godziny. Jest źle , z dwóch powodów: 1) może nie działać we wszystkich lokalizacjach i 2) jest to najwolniejszy możliwy sposób, aby to zrobić ... i nie tylko trochę; jest to rząd wielkości lub dwa wolniejsze niż inne opcje.


Aktualizacja Ostatnio zdobywałem trochę głosów, dlatego chcę dodać do tego, że odkąd to opublikowałem, widziałem dość solidne dowody na to, że Sql Server zoptymalizuje różnicę wydajności między sposobem „poprawnym” a sposobem „szybkim” , co oznacza, że ​​powinieneś teraz faworyzować to pierwsze.

W obu przypadkach chcesz napisać swoje zapytania, aby uniknąć konieczności robienia tego w pierwszej kolejności . Bardzo rzadko powinieneś wykonywać tę pracę w bazie danych.

W większości miejsc baza danych jest już twoim wąskim gardłem. Zasadniczo jest to serwer, który jest najdroższym do dodania sprzętu w celu poprawy wydajności i najtrudniejszy do prawidłowego dodania tych dodatków (na przykład musisz zrównoważyć dyski z pamięcią). Najtrudniej jest też skalować na zewnątrz, zarówno pod względem technicznym, jak i biznesowym; z technicznego punktu widzenia znacznie łatwiej jest dodać serwer WWW lub serwer aplikacji niż serwer bazy danych, a nawet jeśli to nieprawda, nie płacisz ponad 20 000 USD za licencję na serwer dla IIS lub apache.

Chodzi mi o to, aby w miarę możliwości wykonywać tę pracę na poziomie aplikacji. Tylko raz powinny kiedykolwiek znajdziesz się obcinanie datetime SQL Server jest, gdy trzeba grupie przez cały dzień, a nawet wtedy powinieneś mieć dodatkową kolumnę utworzone jako kolumna obliczana, utrzymany w momencie wstawiania / aktualizacji lub utrzymywane w logice aplikacji. Pobierz tę przełamującą indeks, obciążającą procesor bazę danych.


6
„szybki sposób” jest wciąż najszybszym sposobem na sql 2008, zgodnie z testem, który właśnie uruchomiłem
Sam Saffron

3
Do Twojej wiadomości: stackoverflow.com/q/1177449/27535 i stackoverflow.com/q/133081/27535 Dateadd / datediff „wygrywa ...”. Dla jednej zmiennej, której to oczywiście obchodzi, i która ma nadzieję, że obliczyłeś kolumny lub ponad milion wierszy :-)
gbn

9
Ten „poprawny” sposób działa tylko przypadkowo. Sposób zapisu jest taki, jakby składnia DateAdd była (interwał, data, przyrost), ale tak nie jest. Jest to (interwał, przyrost, data). Natknąłem się na to, gdy próbowałem skrócić datę do pierwszego miesiąca: SELECT DATEADD (m, 0, DATEDIFF (m, 0, GETDATE ())) nie działa, ale SELECT DATEADD (m, DATEDIFF (m, 0, GETDATE ()), 0) robi. Przynajmniej tak widzę w 2008R2.
Kelly Cline,

1
@ Kelly w 2008R2, dlaczego nie tylko cast(getdate() as date)?
Joel Coehoorn

2
Oni wszystko praca na kolumnie datetime. getdate()tutaj jest stand-in dla dowolnego źródła datetime.
Joel Coehoorn

44

Tylko dla SQL Server 2008

CAST(@SomeDateTime AS Date) 

Jeśli chcesz, prześlij go z powrotem do daty i godziny

CAST(CAST(@SomeDateTime AS Date) As datetime)

Dobra uwaga: wciąż jestem w 2005 r., Więc w 2008 r. Jest to prawdopodobnie nowy „poprawny” sposób, a nawet może pasować do wydajności „szybkiego” sposobu.
Joel Coehoorn

1
Wydajność tego nowego sposobu jest jeszcze szybsza niż „szybka”.
ErikE

21

Aby uzyskać bardziej kompletną odpowiedź, oto działający sposób obcięcia dowolnej części daty w dół i uwzględnienia minut (zastąp GETDATE()ją datą obcięcia ).

Różni się to od przyjętej odpowiedzi, ponieważ można użyć nie tylko dd(dni), ale dowolnej części daty (patrz tutaj ):

dateadd(minute, datediff(minute, 0, GETDATE()), 0)

Zauważ, że w powyższym wyrażeniu 0jest to stała data na początku roku (1900-01-01). Jeśli chcesz obciąć mniejsze części, takie jak sekundy lub milisekundy, musisz przyjąć stałą datę, która jest bliższa dacie, która ma zostać obcięta, aby uniknąć przepełnienia.


1
To było potwornie pomocne. Wszędzie szukałem sposobu na skrócenie daty i godziny w miejscu niższym niż cały dzień.
Michael - Where's Clay Shirky

1
@Michael, dzięki za opinie, dobrze wiedzieć, że pomogło ci!
Lucero

1
+1 to powinno mieć więcej głosów pozytywnych, to świetna odpowiedź, która rozszerza się na wybraną odpowiedź.
jtate

1
Internet wie, że nie musisz ograniczać się do pełnych okresów. Oto przykład 15-minutowych interwałów z podziałem całkowitym:dateadd(minute, datediff(minute, 0, GETDATE()) / 15 * 15, 0)
Michael - Where's Clay Shirky

7

Fragment, który znalazłem w Internecie, gdy musiałem to zrobić, to:

 dateadd(dd,0, datediff(dd,0, YOURDATE))
 e.g.
 dateadd(dd,0, datediff(dd,0, getDate()))

Jestem w 2005 roku, ale myślałem, że 2008 ma do tego jakieś nowe funkcje?
KM.

2
Schludny! Uciekłbym się do podzielenia części daty i użycia obsługi ciągów znaków, aby je z powrotem połączyć. Może nie być istotny, ale SQL2008 ma typ danych wyłącznie z datą bez elementu czasu.
Frans

1
I zauważ, że masz pomieszane operandy DateAdd DateAdd(dd, DateDiff(...), 0). To może cię ugryźć, jeśli nie będziesz ostrożny.
ErikE

1

W SQl 2005 twoja funkcja trunc_date może być zapisana w ten sposób.

(1)

CREATE FUNCTION trunc_date(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
    CAST(FLOOR( CAST( @date AS FLOAT ) )AS DATETIME)
END

Pierwsza metoda jest znacznie czystsza. Używa tylko 3 wywołań metod, w tym końcowej metody CAST () i nie wykonuje konkatenacji ciągów, co jest automatycznym plusem. Co więcej, nie ma tutaj rzutów typu ogromnego. Jeśli możesz sobie wyobrazić, że znaczniki daty / godziny mogą być reprezentowane, to konwersja dat na liczby i powrót do dat jest dość łatwym procesem.

(2)

CREATE FUNCTION trunc_date(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
      SELECT CONVERT(varchar, @date,112)
END

Jeśli obawiasz się wdrożenia Microsoft DataTime (2) lub (3) może być w porządku.

(3)

CREATE FUNCTION trunc_date(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
SELECT CAST((STR( YEAR( @date ) ) + '/' +STR( MONTH( @date ) ) + '/' +STR( DAY(@date ) )
) AS DATETIME
END

Po trzecie, bardziej szczegółowa metoda. Wymaga to podzielenia daty na części roku, miesiąca i dnia, zestawienia ich w formacie „rrrr / mm / dd”, a następnie przeniesienia z powrotem na datę. Ta metoda obejmuje 7 wywołań metody, w tym końcową metodę CAST (), nie wspominając już o konkatenacji ciągów.


1
CONVERT(DATE, <yourdatetime>) or CONVERT(DATE, GetDate()) or CONVERT(DATE, CURRENT_TIMESTAMP)


0

Dla tych z was, którzy przybyli tutaj, szukając sposobu na obcięcie pola DATETIME do mniej niż całego dnia, na przykład co minutę, możesz użyć tego:

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) + (FLOOR((CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT))) * 1440.0) + (3.0/86400000.0)) / 1440.0 AS DATETIME)

więc gdyby dzisiaj był, 2010-11-26 14:54:43.123to by to wróciło 2010-11-26 14:54:00.000.

Aby zmienić interwał, do którego dąży, zamień 1440.0 na liczbę interwałów w ciągu dnia, na przykład:

24hrs          =   24.0  (for every hour)
24hrs / 0.5hrs =   48.0  (for every half hour)
24hrs / (1/60) = 1440.0  (for every minute)

(Zawsze umieszczaj .0na końcu, aby niejawnie rzutować na zmiennoprzecinkowe)


Dla tych, którzy zastanawiają się, do czego (3.0/86400000)służy moje obliczenie, SQL Server 2005 nie wydaje FLOATsię DATETIMEdokładnie rzutować , więc dodaje to 3 milisekundy przed umieszczeniem go na podłodze.


1
Uważaj jednak na błędy zaokrąglania wynikające z limitów precyzji zmiennoprzecinkowej ... ale to również nie działa z datetime2typem danych.
Lucero

W przypadku godziny działa również WYBIERZ DATĘDATA (godzina, DATEDIFF (godzina, 0, GETDATE ()), 0). Minuta też, ale Second spowoduje przepełnienie.
Kelly Cline,

Przesyłanie do float i powrót do datetime nie działa poprawnie .
ErikE

0

To zapytanie powinno dać wynik równoważny do trunc(sysdate)w Oracle.

SELECT  * 
FROM    your_table
WHERE   CONVERT(varchar(12), your_column_name, 101)
      = CONVERT(varchar(12), GETDATE(), 101)

Mam nadzieję że to pomoże!


0

Możesz także wyodrębnić datę using Substringze zmiennej datetime, a rzutowanie z powrotem do datetime zignoruje część czasu.

declare @SomeDate datetime = '2009-05-28 16:30:22'
SELECT cast(substring(convert(varchar(12),@SomeDate,111),0,12) as Datetime) 

Możesz także uzyskać dostęp do części zmiennej datetime i scalić je do skróconej daty konstrukcji, mniej więcej tak:

SELECT cast(DATENAME(year, @Somedate) + '-' + 
       Convert(varchar(2),DATEPART(month, @Somedate)) + '-' +
       DATENAME(day, @Somedate) 
       as datetime)

0

Wyrocznia:

TRUNC(SYSDATE, 'MONTH')

SQL Server:

DATEADD(DAY, - DATEPART(DAY, DateField) + 1, DateField)

Może być podobnie stosowany do obcinania minut lub godzin od daty.



Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.