DATETRUNC (Transact-SQL)

S’applique à : point de terminaison d’analyse SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance SQL dans Microsoft Fabric Warehouse

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.

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.

DATETRUNCaccepte également un littéral de chaîne (de n’importe quel type de chaîne) pouvant être résolu en datetime2(7).

Types 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 millisecond partie date, l’échelle de temps fractionnaire doit être au moins 3. De même, pour tronquer la microsecond partie de date, l’échelle de temps fractionnaire doit être au moins 6. DATETRUNC ne prend pas en charge la nanosecond partie date, 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 obtenu.

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 @@DATEFIRST paramètre avec la week partie date :

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

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 obtenu. 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 jeu de résultats obtenu.

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

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 obtenu. La datetime d’entrée et le paramètre de 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 obtenu. La datetime d’entrée et le paramètre de 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);

Voici le jeu de résultats obtenu. La valeur smalldatetime d’entrée est la même que les 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 d’une datetime à une milliseconde peut générer 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 obtenu. La date tronquée est la même que 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

Notes

Une erreur est générée si la troncation de date tente de revenir à une date avant la date minimale prise en charge par ce type de données. Cette erreur se produit uniquement lors de l’utilisation du week composant DatePart. Il ne peut pas se produire lors de l’utilisation de la iso_week partie date, car tous les types de dates T-SQL utilisent par coïncidence un lundi pour leurs dates minimales. 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 DATEPART erreur est levée si la DATETRUNC fonction, ou le type de données de date d’entrée, ne prend pas en charge le composant datepart utilisé. Cette erreur peut se produire dans les cas suivants :

1. Une datepart non prise en charge par DATETRUNC est utilisée (à savoir weekday, tzoffset ou nanosecond).

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

3. La partie date nécessite une précision d’échelle de temps fractionnaire supérieure à celle prise en charge par le type de données. Pour plus d’informations, 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.
   

Contenu connexe

• @@DATEFIRST (Transact-SQL)
• DATEPART (Transact-SQL)
• Types de données et fonctions de date et d'heure (Transact-SQL)