DATETRUNC (Transact-SQL)
S’applique à :
SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed InstancePoint de terminaison analytique SQL dans Microsoft FabricEntrepôt dans Microsoft Fabric
La fonction DATETRUNC retourne une date d’entrée tronquée en une datepart spécifiée.
Syntaxe
DATETRUNC ( datepart, date )
Arguments
datepart
Spécifie la précision de la troncation. Cette table répertorie toutes les valeurs de datepart valides pour DATETRUNC, étant donné qu’il s’agit également d’une partie valide du type de date entrée.
| datepart | Abréviations | Notes de troncation |
|---|---|---|
| year | yy, yyyy | |
| quarter | qq, q | |
| month | mm, m | |
| dayofyear | dy, y | dayofyear est tronqué de la même façon que jour |
| day | dd, d | jour est tronqué de la même façon que dayofyear |
| week | wk, ww | Tronquez au premier jour de la semaine. Dans T-SQL, le premier jour de la semaine est défini par le paramètre T-SQL @@DATEFIRST. Pour un environnement anglais (États-Unis) @@DATEFIRST a la valeur par défaut de 7 (dimanche). |
| iso_week | isowk, isoww | Tronquez au premier jour de la semaine ISO. Le premier jour de la semaine dans le système de calendrier ISO8601 est lundi. |
| hour | hh | |
| minute | mi, n | |
| second | ss, s | |
| millisecond | ms | |
| microsecond | mcs |
Notes
Les dateparts T-SQL weekday, timezoneoffset et nanosecond ne sont pas prises en charge pour DATETRUNC.
date
Accepte toute expression, colonne ou variable définie par l’utilisateur, qui peut être résolue en n’importe quel type de date ou d’heure T-SQL valide. Les types valides sont :
- smalldatetime
- datetime
- date
- time
- datetime2
- datetimeoffset
Ne confondez pas le paramètre date avec le type de données date.
DATETRUNC accepte également un littéral de chaîne (de n’importe quel type de chaîne) qui peut être résolu en datetime2(7).
Type de retour
Le type de données retourné pour DATETRUNC est dynamique. DATETRUNC retourne une date tronquée du même type de données (et, le cas échéant, la même échelle de temps fractionnaire) que la date entrée. Par exemple, si DATETRUNC a reçu une date entrée datetimeoffset(3), il retourne un datetimeoffset(3). S’il a reçu un littéral de chaîne pouvant être résolu en datetime2(7), DATETRUNC retourne une datetime2(7).
Précision de l’échelle de temps fractionnée
Les millisecondes ont une échelle de temps fractionnelle de 3 (.123), les microsecondes ont une échelle de temps fractionnelle de 6 (.123456) et les nanosecondes ont une échelle de temps fractionnelle de 9 (.123456789). Les types de données heure, datetime2 et datetimeoffset ont une échelle maximale de 7 (.1234567). Par conséquent, pour tronquer la datepart en millisecond, l’échelle de temps fractionnaire doit être au moins 3. De la même manière, pour tronquer la datepart en microsecond, l’échelle de temps fractionnaire doit être au moins 6. DATETRUNC ne prend pas en charge la datepartnanosecond, car aucun type de date T-SQL ne prend en charge une échelle de temps fractionnaire de 9.
Exemples
R. Utiliser différentes options de datepart
Les exemples suivants illustrent l’utilisation de différentes options de datepart :
DECLARE @d datetime2 = '2021-12-08 11:30:15.1234567';
SELECT 'Year', DATETRUNC(year, @d);
SELECT 'Quarter', DATETRUNC(quarter, @d);
SELECT 'Month', DATETRUNC(month, @d);
SELECT 'Week', DATETRUNC(week, @d); -- Using the default DATEFIRST setting value of 7 (U.S. English)
SELECT 'Iso_week', DATETRUNC(iso_week, @d);
SELECT 'DayOfYear', DATETRUNC(dayofyear, @d);
SELECT 'Day', DATETRUNC(day, @d);
SELECT 'Hour', DATETRUNC(hour, @d);
SELECT 'Minute', DATETRUNC(minute, @d);
SELECT 'Second', DATETRUNC(second, @d);
SELECT 'Millisecond', DATETRUNC(millisecond, @d);
SELECT 'Microsecond', DATETRUNC(microsecond, @d);
Voici le jeu de résultats :
Year 2021-01-01 00:00:00.0000000
Quarter 2021-10-01 00:00:00.0000000
Month 2021-12-01 00:00:00.0000000
Week 2021-12-05 00:00:00.0000000
Iso_week 2021-12-06 00:00:00.0000000
DayOfYear 2021-12-08 00:00:00.0000000
Day 2021-12-08 00:00:00.0000000
Hour 2021-12-08 11:00:00.0000000
Minute 2021-12-08 11:30:00.0000000
Second 2021-12-08 11:30:15.0000000
Millisecond 2021-12-08 11:30:15.1230000
Microsecond 2021-12-08 11:30:15.1234560
B. Paramètre @@DATEFIRST
Les exemples suivants illustrent l’utilisation du paramètre @@DATEFIRST avec la datepartweek :
DECLARE @d datetime2 = '2021-11-11 11:11:11.1234567';
SELECT 'Week-7', DATETRUNC(week, @d); -- Uses the default DATEFIRST setting value of 7 (U.S. English)
SET DATEFIRST 6;
SELECT 'Week-6', DATETRUNC(week, @d);
SET DATEFIRST 3;
SELECT 'Week-3', DATETRUNC(week, @d);
Voici le jeu de résultats :
Week-7 2021-11-07 00:00:00.0000000
Week-6 2021-11-06 00:00:00.0000000
Week-3 2021-11-10 00:00:00.0000000
C. Littéraux de date
Les exemples suivants illustrent l’utilisation de littéraux de paramètre date :
SELECT DATETRUNC(month, '1998-03-04');
SELECT DATETRUNC(millisecond, '1998-03-04 10:10:05.1234567');
DECLARE @d1 char(200) = '1998-03-04';
SELECT DATETRUNC(millisecond, @d1);
DECLARE @d2 nvarchar(max) = '1998-03-04 10:10:05';
SELECT DATETRUNC(minute, @d2);
Voici le jeu de résultats (tous les résultats sont de type datetime2(7)) :
1998-03-01 00:00:00.0000000
1998-03-04 10:10:05.1230000
1998-03-04 00:00:00.0000000
1998-03-04 10:10:00.0000000
D. Variables et le paramètre date
L’exemple ci-dessous illustre l’utilisation du paramètre date :
DECLARE @d datetime2 = '1998-12-11 02:03:04.1234567';
SELECT DATETRUNC(day, @d);
Voici le résultat :
1998-12-11 00:00:00.0000000
E. Colonnes et le paramètre date
La colonne TransactionDate de la table Sales.CustomerTransactions sert d’exemple d’argument column pour le paramètre date :
USE WideWorldImporters;
SELECT CustomerTransactionID,
DATETRUNC(month, TransactionDate) AS MonthTransactionOccurred,
InvoiceID,
CustomerID,
TransactionAmount,
SUM(TransactionAmount) OVER (
PARTITION BY CustomerID ORDER BY TransactionDate,
CustomerTransactionID ROWS UNBOUNDED PRECEDING
) AS RunningTotal,
TransactionDate AS ActualTransactionDate
FROM [WideWorldImporters].[Sales].[CustomerTransactions]
WHERE InvoiceID IS NOT NULL
AND DATETRUNC(month, TransactionDate) >= '2015-12-01';
F. Expressions et le paramètre date
Le paramètre date accepte n’importe quelle expression qui peut être résolue en type de date T-SQL ou tout littéral de chaîne pouvant être résolu en datetime2(7). La colonne TransactionDate de la table Sales.CustomerTransactions sert d’argument artificiel pour illustrer l’utilisation d’une expression pour le paramètre date :
SELECT DATETRUNC(m, SYSDATETIME());
SELECT DATETRUNC(yyyy, CONVERT(date, '2021-12-1'));
USE WideWorldImporters;
GO
SELECT DATETRUNC(month, DATEADD(month, 4, TransactionDate))
FROM Sales.CustomerTransactions;
GO
G. Tronquer une date en datepart représentant sa précision maximale
Si la datepart a la même précision maximale d’unité que le type de date entrée, la troncation de la date entrée à cette datepart n’aurait aucun effet.
Exemple 1
DECLARE @d datetime = '2015-04-29 05:06:07.123';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);
Voici le jeu de résultats montrant que la datetime entrée et la date tronquée sont identiques :
Input 2015-04-29 05:06:07.123
Truncated 2015-04-29 05:06:07.123
Exemple 2
DECLARE @d date = '2050-04-04';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(day, @d);
Voici le jeu de résultats montrant que la datetime entrée et la date tronquée sont identiques :
Input 2050-04-04
Truncated 2050-04-04
Exemple 3 : précision smalldatetime
smalldatetime est précis jusqu’à la minute la plus proche, même s’il a un champ pendant quelques secondes. Par conséquent, la troncation jusqu’à la minute la plus proche ou la seconde la plus proche n’aurait aucun effet.
DECLARE @d smalldatetime = '2009-09-11 12:42:12'
SELECT 'Input', @d;
SELECT 'Truncated to minute', DATETRUNC(minute, @d)
SELECT 'Truncated to second', DATETRUNC(second, @d);
Le jeu de résultats montre que la valeur smalldatetime entrée est identique aux deux valeurs tronquées :
Input 2009-09-11 12:42:00
Truncated to minute 2009-09-11 12:42:00
Truncated to second 2009-09-11 12:42:00
Exemple 4 : précision de datetime
datetime est précis jusqu’à 3,33 millisecondes. Par conséquent, la troncation de datetime à une milliseconde peut produire des résultats différents de ce que l’utilisateur attend. Toutefois, cette valeur tronquée est identique à la valeur datetime stockée en interne.
DECLARE @d datetime = '2020-02-02 02:02:02.002';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);
Voici le jeu de résultats montrant que la date tronquée est identique à la date stockée. Cela peut être différent de ce que vous attendez en fonction de l’instruction DECLARE.
Input 2020-02-02 02:02:02.003
Truncated 2020-02-02 02:02:02.003
Remarques
Une erreur DATE TOO SMALL est levée si la troncation de date tente de revenir à une date avant la date minimale prise en charge par ce type de date. Cela se produit uniquement lors de l’utilisation de la datepartweek. Cela ne peut pas se produire lors de l’utilisation de la datepartiso_week, car tous les types de dates T-SQL coïncident dans l’utilisation d’un lundi en guise de date minimale. Voici un exemple avec le message d’erreur de résultat correspondant :
DECLARE @d date= '0001-01-01 00:00:00';
SELECT DATETRUNC(week, @d);
Msg 9837, Level 16, State 3, Line 84
An invalid date value was encountered: The date value is less than the minimum date value allowed for the data type.
Une erreur DATEPART est levée si la datepart utilisée n’est pas prise en charge par la fonction DATETRUNC ou le type de données de date entrée. Cela peut se produire lorsque :
Une datepart non prise en charge par
DATETRUNCest utilisée (à savoirweekday,tzoffsetounanosecond).Une datepart relative à l’heure est utilisée avec le type de données date, ou une datepart relative à la date est utilisée avec le type de données heure. Voici un exemple avec le message d’erreur de résultat correspondant :
DECLARE @d time = '12:12:12.1234567'; SELECT DATETRUNC(year, @d);Msg 9810, Level 16, State 10, Line 78 The datepart year is not supported by date function datetrunc for data type time.La datepart nécessite une précision d’échelle de temps fractionnaire supérieure à ce qui est pris en charge par le type de données (consultez Précision de l’échelle de temps fractionnaire). Voici un exemple avec le message d’erreur de résultat correspondant :
DECLARE @d datetime2(3) = '2021-12-12 12:12:12.12345'; SELECT DATETRUNC(microsecond, @d);Msg 9810, Level 16, State 11, Line 81 The datepart microsecond is not supported by date function datetrunc for data type datetime2.
Voir aussi
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour