DATE_BUCKET (Transact-SQL)

Artikel
10/24/2023

Wichtig

Azure SQL Edge unterstützt die ARM64-Plattform nicht mehr.

Diese Funktion gibt den Datetime-Wert zurück, der dem Start jedes Datetime-Buckets entspricht, aus dem zeitstempel, der durch den origin Parameter definiert wurde, oder dem Standardwert des 1900-01-01 00:00:00.000 Ursprungsparameters, wenn der Origin-Parameter nicht angegeben ist.

Eine Übersicht über alle Datums- und Uhrzeitdatentypen und zugehörigen Funktionen für Transact-SQL finden Sie unter Datums- und Uhrzeitdatentypen und zugehörige Funktionen (Transact-SQL).

Transact-SQL-Syntaxkonventionen

Syntax

DATE_BUCKET (datePart , number , date , origin)

Argumente

datePart

Der Teil des Datums, der mit dem Parameter "number" verwendet wird, wie in der folgenden Tabelle dargestellt. DATE_BUCKET akzeptiert keine benutzerdefinierten Variablenentsprechungen für die datePart-Argumente .

datePart	Abkürzungen
day	dd, d
week	wk, ww
month	mm, m
quarter	qq, q
year	yy, yyyy
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

Zahl

Die ganze Zahl, die die Breite des Buckets in Kombination mit dem DatePart-Argument entscheidet. Dies stellt die Breite der datePart-Buckets aus der Ursprungszeit dar. Dieses Argument muss ein positiver ganzzahliger Wert sein.

date

Ein Ausdruck, der in einen der folgenden Werte aufgelöst werden kann:

date
datetime
datetime2
datetimeoffset
smalldatetime
time

Akzeptiert für Datum DATE_BUCKETeinen Spaltenausdruck, einen Ausdruck oder eine benutzerdefinierte Variable, wenn sie in einen der zuvor Erwähnung datentypen aufgelöst werden.

origin

Ein optionaler Ausdruck, der in einen der folgenden Werte aufgelöst werden kann:

date
datetime
datetime2
datetimeoffset
smalldatetime
time

Der Datentyp für den Ursprung sollte mit dem Datentyp des Datumsparameters übereinstimmen.

DATE_BUCKET verwendet einen Standardursprungdatumswert von 1900-01-01 00:00:00.000, d. h. 12:00 Uhr am Montag, 1. Januar 1900, wenn kein Ursprungswert für die Funktion angegeben ist.

Rückgabetyp

Der Datentyp des Rückgabewerts für diese Methode ist dynamisch. Der Rückgabetyp hängt von dem Argument ab, das für date angegeben wird. Wenn ein gültiger Eingabedatentyp für datum angegeben wird, DATE_BUCKET wird derselbe Datentyp zurückgegeben. DATE_BUCKETlöst einen Fehler aus, wenn für den Datumsparameter ein Zeichenfolgenliteral angegeben ist.

Rückgabewerte

Grundlegendes zur Ausgabe von `DATE_BUCKET`

DATE_BUCKET gibt den aktuellen Datums- oder Uhrzeitwert zurück, der den Parametern "datePart " und "number " entspricht. Gibt z. B. in den folgenden Ausdrücken den Ausgabewert zurück2020-04-13 00:00:00.0000000, DATE_BUCKET da die Ausgabe basierend auf einer Woche Buckets aus der Standardursprungzeit 1900-01-01 00:00:00.000berechnet wird. Der Wert 2020-04-13 00:00:00.0000000 ist 6.276 Wochen vom Ursprungswert 1900-01-01 00:00:00.000 entfernt.

DECLARE @date DATETIME2 = '2020-04-15 21:22:11';

SELECT DATE_BUCKET(WEEK, 1, @date);

Für alle folgenden Ausdrücke wird derselbe Ausgabewert 2020-04-13 00:00:00.0000000 zurückgegeben. Dies liegt daran, dass 2020-04-13 00:00:00.0000000 6.276 Wochen vom Ursprungsdatum entfernt und 6.276 durch 2, 3, 4 und 6 teilbar ist.

DECLARE @date DATETIME2 = '2020-04-15 21:22:11';

SELECT DATE_BUCKET(WEEK, 2, @date);
SELECT DATE_BUCKET(WEEK, 3, @date);
SELECT DATE_BUCKET(WEEK, 4, @date);
SELECT DATE_BUCKET(WEEK, 6, @date);

Die Ausgabe für den unten angegebenen Ausdruck ist 2020-04-06 00:00:00.0000000, der 6275 Wochen von der origin-Standardzeit 1900-01-01 00:00:00.000 entfernt ist.

DECLARE @date DATETIME2 = '2020-04-15 21:22:11';

SELECT DATE_BUCKET(WEEK, 5, @date);

Die Ausgabe für den unten angegebenen Ausdruck ist 2020-06-09 00:00:00.0000000, der 75 Wochen von der angegebenen origin-Zeit 2019-01-01 00:00:00 entfernt ist.

DECLARE @date DATETIME2 = '2020-06-15 21:22:11';
DECLARE @origin DATETIME2 = '2019-01-01 00:00:00';

SELECT DATE_BUCKET(WEEK, 5, @date, @origin);

Hinweise

Verwenden Sie DATE_BUCKET in den folgenden Klauseln:

GROUP BY
HAVING
ORDER BY
SELECT <list>
WHERE

`datePart`-Argument

dayofyear, day und weekday geben den gleichen Wert zurück. Jedes datePart und seine Abkürzungen geben denselben Wert zurück.

Zahlenargument

Das number-Argument kann den Bereich von positiven int-Werten nicht überschreiten. In den folgenden Anweisungen überschreitet das Argument für number den Bereich von int um 1. Die folgende Anweisung gibt die folgende Fehlermeldung zurück: Msg 8115, Level 16, State 2, Line 2. Arithmetic overflow error converting expression to data type int.

DECLARE @date DATETIME2 = '2020-04-30 00:00:00';

SELECT DATE_BUCKET(DAY, 2147483648, @date);

Wenn ein negativer Wert für die Zahl an die DATE_BUCKET Funktion übergeben wird, wird der folgende Fehler zurückgegeben.

Msg 9834, Level 16, State 1, Line 1
Invalid bucket width value passed to DATE_BUCKET function. Only positive values are allowed.

Date-Argument

DATE_BUCKET gibt den Basiswert zurück, der dem Datentyp des Datumsarguments entspricht. Im folgenden Beispiel wird ein Ausgabewert mit dem Datentyp datetime2 zurückgegeben.

SELECT DATE_BUCKET(DAY, 10, SYSUTCDATETIME());

Origin-Argument

Der Datentyp der Ursprungs - und Datumsargumente in muss identisch sein. Wenn unterschiedliche Datentypen verwendet werden, wird ein Fehler generiert.

Beispiele

A. Berechnen von DATE_BUCKET mit einer Bucketbreite von 1 ab der Ursprungszeit

Jede dieser Anweisungen erhöht DATE_BUCKET mit einer Bucketbreite von 1 aus der Ursprungszeit:

DECLARE @date DATETIME2 = '2020-04-30 21:21:21';

SELECT 'Week', DATE_BUCKET(WEEK, 1, @date)
UNION ALL SELECT 'Day', DATE_BUCKET(DAY, 1, @date)
UNION ALL SELECT 'Hour', DATE_BUCKET(HOUR, 1, @date)
UNION ALL SELECT 'Minutes', DATE_BUCKET(MINUTE, 1, @date)
UNION ALL SELECT 'Seconds', DATE_BUCKET(SECOND, 1, @date);

Hier sehen Sie das Ergebnis.

Week    2020-04-27 00:00:00.0000000
Day     2020-04-30 00:00:00.0000000
Hour    2020-04-30 21:00:00.0000000
Minutes 2020-04-30 21:21:00.0000000
Seconds 2020-04-30 21:21:21.0000000

B. Verwenden von Ausdrücken als Argumente für den number-Parameter und den date-Parameter

In diesen Beispielen werden verschiedene Typen von Ausdrücken als Argumente für die Parameter number und date verwendet. Diese Beispiele werden mithilfe der AdventureWorksDW2019 Datenbank erstellt.

Angeben von benutzerdefinierten Variablen als Argumente für number und date

In diesem Beispiel werden benutzerdefinierte Variablen als Argumente für number und date angegeben:

DECLARE @days INT = 365,
    @datetime DATETIME2 = '2000-01-01 01:01:01.1110000';/* 2000 was a leap year */;

SELECT DATE_BUCKET(DAY, @days, @datetime);

Hier sehen Sie das Ergebnis.

---------------------------
1999-12-08 00:00:00.0000000

(1 row affected)

Angeben einer Spalte als date-Parameter

Im folgenden Beispiel berechnen wir die Summe von OrderQuantity und die Summe von UnitPrice, gruppiert über wöchentliche date-Buckets.

SELECT DATE_BUCKET(WEEK, 1, CAST(Shipdate AS DATETIME2)) AS ShippedDateBucket,
    Sum(OrderQuantity) AS SumOrderQuantity,
    Sum(UnitPrice) AS SumUnitPrice
FROM dbo.FactInternetSales FIS
WHERE Shipdate BETWEEN '2011-01-03 00:00:00.000'
        AND '2011-02-28 00:00:00.000'
GROUP BY DATE_BUCKET(week, 1, CAST(Shipdate AS DATETIME2))
ORDER BY ShippedDateBucket;

Hier sehen Sie das Ergebnis.

ShippedDateBucket           SumOrderQuantity SumUnitPrice
--------------------------- ---------------- ---------------------
2011-01-03 00:00:00.0000000 21               65589.7546
2011-01-10 00:00:00.0000000 27               89938.5464
2011-01-17 00:00:00.0000000 31               104404.9064
2011-01-24 00:00:00.0000000 36               118525.6846
2011-01-31 00:00:00.0000000 39               123555.431
2011-02-07 00:00:00.0000000 35               109342.351
2011-02-14 00:00:00.0000000 32               107804.8964
2011-02-21 00:00:00.0000000 37               119456.3428
2011-02-28 00:00:00.0000000 9                28968.6982

Angeben einer skalaren Systemfunktion als Argument für date

In diesem Beispiel wird SYSDATETIME für date angegeben. Welcher Wert genau zurückgegeben wird, hängt davon ab, an welchem Tag und zu welcher Uhrzeit die Anweisung ausgeführt wird:

SELECT DATE_BUCKET(WEEK, 10, SYSDATETIME());

Hier sehen Sie das Ergebnis.

---------------------------
2020-03-02 00:00:00.0000000

(1 row affected)

Angeben von skalaren Unterabfragen und skalaren Funktionen als Argumente für number und date

In diesem Beispiel werden skalare Unterabfragen (MAX(OrderDate)) als Argumente für number und date verwendet. (SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100) dient als Beispielargument für den Parameter „number“, das veranschaulicht, wie ein number-Argument aus einer Werteliste ausgewählt wird.

SELECT DATE_BUCKET(WEEK,
        (
            SELECT TOP 1 CustomerKey
            FROM dbo.DimCustomer
            WHERE GeographyKey > 100
        ),
        (
            SELECT MAX(OrderDate)
            FROM dbo.FactInternetSales
        )
    );

Angeben von numerischen Ausdrücken und skalaren Systemfunktionen als Argumente für number und date

In diesem Beispiel werden ein numerischer Ausdruck ((10/2)) und Skalarsystemfunktionen (SYSDATETIME) als Argumente für number und date verwendet.

SELECT DATE_BUCKET(WEEK, (10 / 2), SYSDATETIME());

Angeben einer Aggregatfensterfunktion als Argument für number

In diesem Beispiel wird eine Aggregatfensterfunktion als Argument für number verwendet.

SELECT DISTINCT DATE_BUCKET(DAY, 30, CAST([shipdate] AS DATETIME2)) AS DateBucket,
    FIRST_VALUE([SalesOrderNumber]) OVER (
        ORDER BY DATE_BUCKET(DAY, 30, CAST([shipdate] AS DATETIME2))
        ) AS FIRST_VALUE_In_Bucket,
    LAST_VALUE([SalesOrderNumber]) OVER (
        ORDER BY DATE_BUCKET(DAY, 30, CAST([shipdate] AS DATETIME2))
        ) AS LAST_VALUE_In_Bucket
FROM [dbo].[FactInternetSales]
WHERE ShipDate BETWEEN '2011-01-03 00:00:00.000'
        AND '2011-02-28 00:00:00.000'
ORDER BY DateBucket;
GO

C. Verwenden eines nicht standardmäßigen Ursprungswerts

In diesem Beispiel wird ein nicht standardmäßiger Ursprungswert für date-Buckets verwendet.

DECLARE @date DATETIME2 = '2020-06-15 21:22:11';
DECLARE @origin DATETIME2 = '2019-01-01 00:00:00';

SELECT DATE_BUCKET(HOUR, 2, @date, @origin);

Weitere Informationen

CAST und CONVERT (Transact-SQL)

DATE_BUCKET (Transact-SQL)

Syntax

Argumente

datePart

Zahl

date

origin

Rückgabetyp

Rückgabewerte

Grundlegendes zur Ausgabe von DATE_BUCKET

Hinweise

datePart-Argument

Zahlenargument

Date-Argument

Origin-Argument

Beispiele

A. Berechnen von DATE_BUCKET mit einer Bucketbreite von 1 ab der Ursprungszeit

B. Verwenden von Ausdrücken als Argumente für den number-Parameter und den date-Parameter

Angeben von benutzerdefinierten Variablen als Argumente für number und date

Angeben einer Spalte als date-Parameter

Angeben einer skalaren Systemfunktion als Argument für date

Angeben von skalaren Unterabfragen und skalaren Funktionen als Argumente für number und date

Angeben von numerischen Ausdrücken und skalaren Systemfunktionen als Argumente für number und date

Angeben einer Aggregatfensterfunktion als Argument für number

C. Verwenden eines nicht standardmäßigen Ursprungswerts

Weitere Informationen

Zusätzliche Ressourcen

Grundlegendes zur Ausgabe von `DATE_BUCKET`

`datePart`-Argument