Partager via

DATE_BUCKET (Transact-SQL)

  • Article

Important

Azure SQL Edge sera mis hors service le 30 septembre 2025. Pour plus d’informations et pour connaître les options de migration, consultez l’Avis de mise hors service.

Remarque

Azure SQL Edge ne prend plus en charge la plateforme ARM64.

Cette fonction retourne la valeur datetime correspondant au début de chaque compartiment datetime, à partir de l’horodatage défini par le origin paramètre ou de la valeur d’origine par défaut de 1900-01-01 00:00:00.000 si le paramètre d’origine n’est pas spécifié.

Pour obtenir une vue d’ensemble de tous les types de données et toutes les fonctions de date et d’heure Transact-SQL, consultez Types de données et fonctions de date et d’heure (Transact-SQL).

Conventions syntaxiques de Transact-SQL

Syntaxe

DATE_BUCKET (datePart , number , date , origin)

Arguments

datePart

Partie de la date utilisée avec le paramètre « number », comme indiqué dans le tableau suivant. DATE_BUCKET n’accepte pas les équivalents de variables définis par l’utilisateur pour les arguments datePart .

datePart Abréviations
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

number

Nombre entier qui détermine la largeur du compartiment combiné à l’argument datePart . Cela représente la largeur des compartiments datePart de l’heure d’origine. Cet argument doit être une valeur entière positive .

date

Expression qui peut être résolue en valeur, parmi les suivantes :

  • date
  • datetime
  • datetime2
  • datetimeoffset
  • smalldatetime
  • time

Pour la date, DATE_BUCKET accepte une expression de colonne, une expression ou une variable définie par l’utilisateur si elles sont résolues vers l’un des types de données mentionnés précédemment.

origin

Expression facultative qui peut être résolue en l’une des valeurs suivantes :

  • date
  • datetime
  • datetime2
  • datetimeoffset
  • smalldatetime
  • time

Le type de données d’origine doit correspondre au type de données du paramètre de date.

DATE_BUCKETutilise une valeur de date d’origine par défaut , 1900-01-01 00:00:00.000autrement dit, 12:00 le lundi 1er janvier 1900, si aucune valeur d’origine n’est spécifiée pour la fonction.

Type renvoyé

Le type de données de la valeur de retour pour cette méthode est dynamique. Le type de retour dépend de l’argument fourni pour date. Si un type de données d’entrée valide est fourni pour la date, DATE_BUCKET retourne le même type de données. DATE_BUCKET génère une erreur si un littéral de chaîne est spécifié pour le paramètre de date .

Valeurs de retour

Comprendre la sortie de DATE_BUCKET

DATE_BUCKET retourne la valeur de date ou d’heure la plus récente, correspondant aux paramètres datePart et nombre . Par exemple, dans les expressions suivantes, DATE_BUCKET retourne la valeur de 2020-04-13 00:00:00.0000000sortie , car la sortie est calculée en fonction des compartiments d’une semaine à partir de l’heure d’origine par défaut de 1900-01-01 00:00:00.000. La valeur 2020-04-13 00:00:00.0000000 correspond à 6 276 semaines à partir de la valeur d’origine de 1900-01-01 00:00:00.000.

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

SELECT DATE_BUCKET(WEEK, 1, @date);

Pour toutes les expressions suivantes, la même valeur de 2020-04-13 00:00:00.0000000 sortie est retournée. Cela est dû au fait que 2020-04-13 00:00:00.0000000 correspond à 6 276 semaines par rapport à la date d’origine et que 6276 est divisible par 2, 3, 4 et 6.

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);

La sortie de l’expression ci-dessous est 2020-04-06 00:00:00.0000000, correspondant à 6 275 semaines à partir de la date d’origine par défaut 1900-01-01 00:00:00.000.

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

SELECT DATE_BUCKET(WEEK, 5, @date);

La sortie de l’expression ci-dessous est 2020-06-09 00:00:00.0000000, correspondant à 75 semaines à partir de la date d’origine spécifiée 2019-01-01 00:00:00.

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);

Notes

Utilisez DATE_BUCKET dans les clauses suivantes :

  • GROUP BY
  • HAVING
  • ORDER BY
  • SELECT <list>
  • WHERE

Argument datePart

dayofyear, day et weekday renvoient la même valeur. Chaque datePart et ses abréviations retournent la même valeur.

nombre d’arguments

L’argument numéro ne peut pas dépasser la plage des valeurs int positives. Dans les instructions suivantes, l’argument pour number dépasse la plage des int de 1. L’instruction suivante retourne le message d’erreur suivant : 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);

Si une valeur négative pour le nombre est passée à la DATE_BUCKET fonction, l’erreur suivante est retournée.

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

argument date

DATE_BUCKET retourne la valeur de base correspondant au type de données de l’argument date . Dans l’exemple suivant, une valeur de sortie avec le type de données datetime2 est retournée.

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

argument d’origine

Le type de données des arguments d’origine et de date doit être identique. Si différents types de données sont utilisés, une erreur est générée.

Exemples

R. Calcul de DATE_BUCKET avec une largeur de compartiment de 1 à partir de l’heure d’origine

Chacune de ces instructions incrémente DATE_BUCKET avec une largeur de compartiment de 1 à partir de l’heure d’origine :

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);

Voici le jeu de résultats.

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. Utiliser des expressions comme arguments pour les paramètres de date et de numéro

Ces exemples utilisent différents types d’expressions comme arguments pour les paramètres number et date. Ces exemples sont générés à l’aide de la AdventureWorksDW2019 base de données.

Spécifier des variables définies par l'utilisateur comme numéro et date

Cet exemple spécifie des variables définies par l’utilisateur comme arguments pour number et date :

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

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

Voici le jeu de résultats.

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

(1 row affected)

Spécifier une colonne comme date

Dans l’exemple ci-dessous, nous calculons les sommes OrderQuantity et UnitPrice regroupées sur les compartiments de dates hebdomadaires.

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;

Voici le jeu de résultats.

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

Spécifier la fonction système scalaire comme date

Cet exemple spécifie SYSDATETIME pour date. La valeur exacte retournée varie selon le jour et l’heure de l’exécution de l’instruction :

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

Voici le jeu de résultats.

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

(1 row affected)

Spécifier des sous-requêtes scalaires et de fonctions scalaires comme numéro et date

Cet exemple utilise des sous-requêtes scalaires, MAX(OrderDate), comme arguments pour number et date. (SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100) sert d’argument artificiel pour le paramètre number pour illustrer la sélection d’un argument number dans une liste de valeurs.

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

Spécifier des expressions numériques et de fonctions système scalaires comme numéro et date

Cet exemple utilise une expression numérique [(10/2)] et des fonctions système scalaires (SYSDATETIME) comme arguments pour number et date.

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

Spécifier une fonction d'agrégation de fenêtres comme numéro

Cet exemple utilise une fonction d’agrégation comme argument pour number.

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. Utiliser une valeur d’origine différente de la valeur par défaut

Cet exemple utilise une valeur d’origine différente de la valeur par défaut pour générer les compartiments de date.

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);

Contenu connexe