Partager via

DATE_BUCKET (Transact-SQL)

S’applique à : SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed Instance Azure SQL Edge SQL database in Microsoft Fabric

Cette fonction retourne la valeur date-heure correspondant au début de chaque compartiment date-heure à partir de l’horodatage défini par le paramètre d’origine , 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é.

Consultez les types et fonctions de données date et heure pour obtenir une vue d’ensemble de tous les types et fonctions de données 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 nombre , 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 semaine,ww
month Mm, M
quarter qq,q
year aaaa, aaaa
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. Cet argument représente la largeur des compartiments de parties de date de l’heure d’origine. Cet argument ne peut pas être une valeur entière négative.

date

Expression qui doit être l’une des valeurs 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_BUCKET utilise une valeur de date d’origine par défaut correspondant 1900-01-01 00:00:00.000 à 12h00 le lundi 1er janvier 1900, si aucune valeur d’origine n’est spécifiée pour la fonction.

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

Valeur retournée

Comprendre la sortie de 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 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 AS 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 6,276 est divisible par 2, 3, 4 et 6.

DECLARE @date AS 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 suivante est , qui est 2020-04-06 00:00:00.0000000de 6 275 semaines à partir de l’heure 1900-01-01 00:00:00.000d’origine par défaut .

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

SELECT DATE_BUCKET(WEEK, 5, @date);

La sortie de l’expression suivante est , qui est 2020-06-09 00:00:00.0000000de 75 semaines à partir de l’heure 2019-01-01 00:00:00d’origine spécifiée .

DECLARE @date AS DATETIME2 = '2020-06-15 21:22:11';
DECLARE @origin AS 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érique

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 AS 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 de 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.

Notes

Utilisez DATE_BUCKET dans les clauses suivantes :

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

Exemples

Les exemples de code de cet article utilisent les bases de données d'exemple AdventureWorks2025 ou AdventureWorksDW2025, que vous pouvez télécharger à partir de la page d'accueil Microsoft SQL Server Samples and Community Projects.

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 AS 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 AdventureWorksDW2025.

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 AS INT = 365,
        @datetime AS DATETIME2 = '2000-01-01 01:01:01.1110000';

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 suivant, nous calculons la somme et OrderQuantity la somme des compartiments de UnitPrice dates regroupés sur des 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 AS 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 le nombre et la 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 AS DATETIME2 = '2020-06-15 21:22:11';
DECLARE @origin AS DATETIME2 = '2019-01-01 00:00:00';

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

Contenu connexe

  • Last updated on