Partager via

DATE_BUCKET (Transact-SQL)

  • Article

S’applique à : SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance Azure SQL Edge

Cette fonction retourne la valeur date-heure correspondant au début de chaque compartiment date-heure, à partir du timestamp défini par le paramètre origin ou 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 de la syntaxe Transact-SQL

Syntaxe

DATE_BUCKET (datepart, number, date [, origin ] )

Arguments

datepart

Partie de la date utilisée avec le paramètre number, par exemple, année, mois, jour, minute, seconde.

DATE_BUCKET n’accepte pas d’é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é avec l’argument datepart. Cela correspond à la largeur des compartiments datepart à partir de l’heure d’origine. Cet argument ne peut pas être une valeur entière négative.

date

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

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

Pour date, DATE_BUCKET accepte une expression de colonne, une expression ou une variable définie par l’utilisateur si elle est résolue en l’un des types de données mentionnés ci-dessus.

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 pour origin doit correspondre au type de données du paramètre date.

DATE_BUCKET utilise une valeur de date d’origine par défaut de 1900-01-01 00:00:00.000 soit 12h00 le lundi 1 janvier 1900, si aucune valeur origin n’est spécifiée pour la fonction.

Type de retour

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 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 date.

Valeurs retournées

Présentation de la sortie depuis DATE_BUCKET

DATE_BUCKET retourne la dernière valeur de date ou de temps correspondant au paramètre datepart et numéro. Par exemple, dans les expressions ci-dessous, DATE_BUCKET retourne la valeur de sortie de 2020-04-13 00:00:00.0000000, 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 ci-dessous, la même valeur de sortie de 2020-04-13 00:00:00.0000000 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 6,276 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);

argument datepart

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

argument numéro

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 : 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 number négative est transmise à la fonction DATE_BUCKET, 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 accompagnée du type de données datetime2 est retournée.

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

argument origin

Le type de données des arguments origin et date doit être le même. Si des types de données différents sont utilisés, une erreur est générée.

Notes

Utilisez DATE_BUCKET dans les clauses suivantes :

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

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 base de données AdventureWorksDW2022.

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

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

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 un numéro et une 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);

Voir aussi