DATE_BUCKET (Transact-SQL)
S’applique à :
SQL Server 2022 (16.x) Azure SQL DatabaseAzure 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
Commentaires
Bientôt disponible : Tout au long de l’année 2024, nous abandonnerons progressivement le mécanisme de retour d’information GitHub Issues pour le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez : https://aka.ms/ContentUserFeedback.
Soumettre et afficher des commentaires pour