Data i godzina T-SQL zaokrąglona do najbliższej minuty i najbliższych godzin przy użyciu funkcji

113

W SQL Server 2008 chciałbym, aby kolumna z datą i godziną była zaokrąglona do najbliższej godziny i najbliższej minuty, najlepiej z istniejącymi funkcjami w 2008 roku.

Dla tej wartości kolumny 2007-09-22 15:07:38.850wynik będzie wyglądał następująco:

2007-09-22 15:08 -- nearest minute
2007-09-22 15    -- nearest hour

— user219628
źródło

6

Czy najbliższa minuta nie powinna wskazywać 15:08? Ponieważ sekunda w minucie to 60 ...

— OMG Kucyki

Czy celowo nie poprawiłeś tego błędu, redagując jego pytanie, aby móc skomentować?

— Pan środa,

@MrWedroda Zdajesz sobie sprawę, że między zmianą a komentarzem upłynęło ponad 10 minut. Wyobrażam sobie, że ta myśl pojawiła się później.

— lc.

208

declare @dt datetime

set @dt = '09-22-2007 15:07:38.850'

select dateadd(mi, datediff(mi, 0, @dt), 0)
select dateadd(hour, datediff(hour, 0, @dt), 0)

wróci

2007-09-22 15:07:00.000
2007-09-22 15:00:00.000

Powyższe tylko skraca sekundy i minuty, dając wyniki, o które pytano. Jak zauważył @OMG Kucyki, jeśli chcesz zaokrąglić w górę / w dół, możesz dodać odpowiednio pół minuty lub pół godziny, a następnie skrócić:

select dateadd(mi, datediff(mi, 0, dateadd(s, 30, @dt)), 0)
select dateadd(hour, datediff(hour, 0, dateadd(mi, 30, @dt)), 0)

a otrzymasz:

2007-09-22 15:08:00.000
2007-09-22 15:00:00.000

Zanim typ danych data został dodany w SQL Server 2008, użyłbym powyższej metody, aby skrócić część czasu z daty i godziny, aby uzyskać tylko datę. Chodzi o to, aby określić liczbę dni między daną datą i godziną a ustalonym punktem w czasie ( 0który niejawnie rzutuje na 1900-01-01 00:00:00.000):

declare @days int
set @days = datediff(day, 0, @dt)

a następnie dodaj tę liczbę dni do ustalonego punktu w czasie, co daje pierwotną datę z czasem ustawionym na 00:00:00.000:

select dateadd(day, @days, 0)

lub bardziej zwięźle:

select dateadd(day, datediff(day, 0, @dt), 0)

Użycie innego datepart (np hour, mi) będzie działać odpowiednio.

— Jeff Ogata
źródło

2

Wątpię, czy ktokolwiek inny wpadnie na to, ale jeśli próbujesz zaokrąglić W GÓRĘ do najbliższej sekundy i dodać 500 milisekund, zechcesz zrobić datediff (sekunda, „1/1/2000”, .... vs datediff (po drugie, 0 .... ponieważ pojawi się błąd przepełnienia.

— Wydaje

„Dodaj liczbę godzin od 1 stycznia 1900 r. Do 1 stycznia 1900 r.” - napotkałem to w Javie / SQL, gdzie wygląda brzydko. W moim przypadku naprawdę należało to zrobić po stronie Javy.

— Corwin Newall,

W przypadku obliczeń z datetimeoffsetmusiałem zastąpić 0go, TODATETIMEOFFSET('1900-01-01 00:00:00', 0)aby uniknąć narzucania lokalnej strefy czasowej na wynik.

— krlmlr

26

„Zaokrąglone” w dół, jak w przykładzie. Spowoduje to zwrócenie wartości varchar daty.

DECLARE @date As DateTime2
SET @date = '2007-09-22 15:07:38.850'

SELECT CONVERT(VARCHAR(16), @date, 120) --2007-09-22 15:07
SELECT CONVERT(VARCHAR(13), @date, 120) --2007-09-22 15

— Magnus
źródło

Konwersja varchar z powrotem do datetime może zrobić (na godzinę):CONVERT(datetime, CONVERT(VARCHAR(13), @date, 120)+':00:00')

— Decula

10

Zdaję sobie sprawę, że to pytanie jest starożytne i istnieje akceptowana i alternatywna odpowiedź. Zdaję sobie również sprawę, że moja odpowiedź odpowie tylko na połowę pytania, ale dla każdego, kto chce zaokrąglić do najbliższej minuty i nadal mieć wartość zgodną z datą i godziną, używając tylko jednej funkcji :

CAST(YourValueHere as smalldatetime);

Przez godziny lub sekundy używaj powyższej odpowiedzi Jeffa Ogaty (zaakceptowanej odpowiedzi).

— Andrew Steitz
źródło

1

Doskonała odpowiedź, sprawdziłem, że robi okrągłe, a nie tylko obcięte. Dla każdego, kto szukał tej opcji, smalldatetimezostał dodany w SQL 2008.

— BradC

Jest to najlepsza opcja, jeśli potrzebujesz poprawić niektóre dane w tabeli, co wymaga tylko minuty.

— Szyfrowanie

-1

Select convert(char(8), DATEADD(MINUTE, DATEDIFF(MINUTE, 0, getdate), 0), 108) as Time

zaokrągli w dół sekundy do 00

— Jam_Jam
źródło

Bardzo wolno i nie zaokrągla, o co prosił PO, ale zaokrągla w dół, o co nie prosił. (Zastrzeżenie - nie głosowałem w dół)

— Reversed Engineer