DATETRUNC (Transact-SQL)

Article
10/31/2024

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.

Remarque

DATETRUNC a été introduit dans SQL Server 2022 (16.x).

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 manière que `day`
`day`	`dd`, `d`	`day` est tronqué de la même manière 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 américain, `@@DATEFIRST` la `7` valeur par défaut est (dimanche).
`iso_week`	`isowk`, `isoww`	Tronquer au premier jour d’une 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`

Remarque

Les parties de dates T-SQL de la semaine, timezoneoffset et nanosecondes 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.

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.

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.

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

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

Une datepart non prise en charge par DATETRUNC est utilisée (à savoir weekday, tzoffset ou nanosecond).
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 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.
```

Partage via

DATETRUNC (Transact-SQL)

Syntaxe

Arguments

datepart

date

Types de retour

Précision de l’échelle de temps fractionnée

Exemples

R. Utiliser différentes options de datepart

B. Paramètre @@DATEFIRST

C. Littéraux de date

D. Variables et le paramètre date

E. Colonnes et le paramètre date

F. Expressions et le paramètre date

G. Tronquer une date en datepart représentant sa précision maximale

Exemple 1

Exemple 2

Exemple 3 : précision smalldatetime

Exemple 4 : précision de datetime

Notes

Commentaires

Ressources supplémentaires

Partage via

DATETRUNC (Transact-SQL)

Syntaxe

Arguments

datepart

date

Types de retour

Précision de l’échelle de temps fractionnée

Exemples

R. Utiliser différentes options de datepart

B. Paramètre @@DATEFIRST

C. Littéraux de date

D. Variables et le paramètre date

E. Colonnes et le paramètre date

F. Expressions et le paramètre date

G. Tronquer une date en datepart représentant sa précision maximale

Exemple 1

Exemple 2

Exemple 3 : précision smalldatetime

Exemple 4 : précision de datetime

Notes

Contenu connexe

Commentaires

Ressources supplémentaires