Partager via

DATEDIFF (Transact-SQL)

  • Article

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Cette fonction retourne le nombre (valeur entière signée) de limites datepart spécifiées, traversées entre les valeurs startdate et enddate spécifiées.

Consultez DATEDIFF_BIG pour une fonction qui gère des différences plus importantes entre les valeurs startdate et enddate. 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

DATEDIFF ( datepart , startdate , enddate )

Arguments

datepart

Spécifie les unités dans lesquelles DATEDIFF indique la différence entre la date de début et la date de fin. Parmi les unités datepart couramment utilisées, citons month et second.

La valeur datepart ne peut pas être spécifiée dans une variable, ni sous forme de chaîne entre guillemets comme 'month'.

Le tableau suivant liste toutes les valeurs datepart valides. DATEDIFF accepte le nom complet de la partie date ou toute abréviation répertoriée du nom complet.

Nom datepart Abréviation datepart
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns

Chaque nom et abréviations de datepart spécifiques pour ce nom de partie de date retourne la même valeur.

startdate

Expression qui peut être résolue en valeur, parmi les suivantes :

  • date
  • datetime
  • datetimeoffset
  • datetime2
  • smalldatetime
  • time

Pour éviter toute ambiguïté, utilisez des années à quatre chiffres. Consultez configuration du serveur : coupure de deux chiffres de l’année pour plus d’informations sur les valeurs d’année à deux chiffres.

enddate

Consultez startdate.

Types de retour

int

Valeur retournée

Différence int entre startdate et enddate, exprimée dans le jeu de limites par datepart.

Par exemple, retourne -2, SELECT DATEDIFF(day, '2036-03-01', '2036-02-28'); indiquant que 2036 doit être une année bissextile. Ce cas signifie que si nous commençons à la date de début, puis comptez -2 les jours, nous atteignons la date de fin de 2036-02-28.2036-03-01

Si une valeur de retour est hors limites pour int (-2 147 483 648 à +2 147 483 647), DATEDIFF retourne une erreur. Pour millisecond, la différence maximale entre la date de début et la date de fin est de 24 jours, 20 heures, 31 minutes et 23,647 secondes. Pour second, la différence maximale est de 68 ans, 19 jours, 3 heures, 14 minutes et 7 secondes.

Si la date de début et la date de fin ne sont affectées qu’une valeur d’heure, et que la partie date n’est pas une datepart d’heure, DATEDIFF retourne 0.

DATEDIFF utilise le composant de décalage de fuseau horaire de startdate ou enddate pour calculer la valeur de retour.

Étant donné que smalldatetime est exact uniquement à la minute, les secondes et millisecondes sont toujours définies 0 dans la valeur de retour lorsque startdate ou enddate ont une valeur smalldatetime.

Si seule une valeur d’heure est affectée à une variable d’un type de données date, DATEDIFF définit la partie date manquante sur la valeur par défaut :1900-01-01. Si seule une valeur de date est affectée à une variable d’un type de données date ou heure, DATEDIFF définit la partie heure manquante sur la valeur par défaut : 00:00:00. Si startdate ou enddate a uniquement une partie heure et que l’autre a uniquement une partie date, DATEDIFF affecte aux parties heure et date manquantes les valeurs par défaut.

Si la date de début et la date de fin ont des types de données de date différents, et qu’une partie de temps ou une précision fractionnaire de secondes est supérieure à l’autre, DATEDIFF définit les parties manquantes de l’autre sur 0.

Limites de datepart

Les instructions suivantes ont les mêmes valeurs startdate et enddate. Ces dates sont adjacentes et ont une différence d’une centaine de nanosecondes (0,0000001 seconde). La différence entre les startdate et endate dans chaque instruction traverse une limite d’heure ou de calendrier de son datepart. Chaque instruction retourne 1.

SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(weekday, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(microsecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

Si startdate et enddate ont des valeurs d’année différentes, mais qu’elles ont les mêmes valeurs de semaine civile, DATEDIFF retourne 0 pour datepart week.

Notes

Utilisez DATEDIFF dans les SELECT <list>clauses , , GROUP BYWHEREHAVINGet ORDER BY les clauses.

DATEDIFF caste implicitement les littéraux de chaîne en type datetime2. En d’autres termes, DATEDIFF ne prend pas en charge le format YDM lorsque la date est passée sous forme de chaîne. Vous devez convertir explicitement la chaîne en type datetime ou smalldatetime pour utiliser le YDM format.

La spécification de SET DATEFIRST n’a pas d’effet sur DATEDIFF. DATEDIFF utilise toujours Dimanche comme premier jour de la semaine pour que la fonction soit déterministe.

DATEDIFF peut dépasser une précision ou minute une valeur supérieure si la différence entre enddate et startdate retourne une valeur hors plage pour int.

Exemples

Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 ou AdventureWorksDW2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires Microsoft SQL Server.

Ces exemples utilisent différents types d’expressions comme arguments pour les paramètres startdate et enddate.

R : Spécifier des colonnes pour startdate et enddate

Cet exemple calcule le nombre de limites de jour qui sont traversées entre les dates de deux colonnes dans une table.

CREATE TABLE dbo.Duration
(
    startDate DATETIME2,
    endDate DATETIME2
);

INSERT INTO dbo.Duration (startDate, endDate)
VALUES ('2007-05-06 12:10:09', '2007-05-07 12:10:09');

SELECT DATEDIFF(day, startDate, endDate) AS [Duration]
FROM dbo.Duration;

Voici le jeu de résultats.

Duration
--------
1

B. Spécifier des variables définies par l’utilisateur pour startdate et enddate

Dans cet exemple, les variables définies par l’utilisateur font office d’arguments pour startdate et enddate.

DECLARE @startdate AS DATETIME2 = '2007-05-05 12:10:09.3312722';
DECLARE @enddate AS DATETIME2 = '2007-05-04 12:10:09.3312722';

SELECT DATEDIFF(day, @startdate, @enddate);

C. Spécifier les fonctions système scalaires pour la date de début et la date de fin

Cet exemple utilise des fonctions système scalaires comme arguments pour startdate et enddate.

SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME());

D. Spécifier des sous-requêtes scalaires et des fonctions scalaires pour la date de début et la date de fin

Cet exemple utilise des sous-requêtes et des fonctions scalaires comme arguments pour startdate et enddate.

USE AdventureWorks2022;
GO

SELECT DATEDIFF(day, (SELECT MIN(OrderDate)
                      FROM Sales.SalesOrderHeader), (SELECT MAX(OrderDate)
                                                    FROM Sales.SalesOrderHeader));

E. Spécifier des constantes pour la date de début et la date de fin

Cet exemple suivant des constantes à caractères comme arguments pour startdate et enddate.

SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', '2007-05-08 09:53:01.0376635');

F. Spécifier des expressions numériques et des fonctions système scalaires pour enddate

Ce exemple utilise une expression numérique, (GETDATE() + 1) et des fonctions système scalaires, GETDATE et SYSDATETIME, comme arguments pour enddate.

USE AdventureWorks2022;
GO

SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', GETDATE() + 1) AS NumberOfDays
FROM Sales.SalesOrderHeader;
GO

USE AdventureWorks2022;
GO

SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', DATEADD(day, 1, SYSDATETIME())) AS NumberOfDays
FROM Sales.SalesOrderHeader;
GO

G. Spécifier des fonctions de classement pour startdate

Cet exemple utilise une fonction de classement comme argument pour startdate.

USE AdventureWorks2022;
GO

SELECT p.FirstName,
       p.LastName,
       DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY a.PostalCode), SYSDATETIME()) AS 'Row Number'
FROM Sales.SalesPerson AS s
     INNER JOIN Person.Person AS p
         ON s.BusinessEntityID = p.BusinessEntityID
     INNER JOIN Person.Address AS a
         ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
      AND SalesYTD <> 0;

H. Spécifier une fonction de fenêtre d’agrégation pour startdate

Cet exemple utilise une fonction d’agrégation comme argument pour startdate.

USE AdventureWorks2022;
GO

SELECT soh.SalesOrderID,
       sod.ProductID,
       sod.OrderQty,
       soh.OrderDate,
       DATEDIFF(day, MIN(soh.OrderDate) OVER (PARTITION BY soh.SalesOrderID), SYSDATETIME()) AS 'Total'
FROM Sales.SalesOrderDetail AS sod
     INNER JOIN Sales.SalesOrderHeader AS soh
         ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.SalesOrderID IN (43659, 58918);
GO

I. Rechercher la différence entre la date de début et la date de fin sous forme de chaînes de parties de date

-- DOES NOT ACCOUNT FOR LEAP YEARS
DECLARE @date1 AS DATETIME, @date2 AS DATETIME, @result AS VARCHAR (100);

DECLARE @years AS INT, @months AS INT, @days AS INT, @hours AS INT, @minutes AS INT, @seconds AS INT, @milliseconds AS INT;

SET @date1 = '1900-01-01 00:00:00.000';

SET @date2 = '2018-12-12 07:08:01.123';

SELECT @years = DATEDIFF(yy, @date1, @date2);

IF DATEADD(yy, -@years, @date2) < @date1
    SELECT @years = @years - 1;

SET @date2 = DATEADD(yy, -@years, @date2);

SELECT @months = DATEDIFF(mm, @date1, @date2);

IF DATEADD(mm, -@months, @date2) < @date1
    SELECT @months = @months - 1;

SET @date2 = DATEADD(mm, -@months, @date2);

SELECT @days = DATEDIFF(dd, @date1, @date2);

IF DATEADD(dd, -@days, @date2) < @date1
    SELECT @days = @days - 1;

SET @date2 = DATEADD(dd, -@days, @date2);

SELECT @hours = DATEDIFF(hh, @date1, @date2);

IF DATEADD(hh, -@hours, @date2) < @date1
    SELECT @hours = @hours - 1;

SET @date2 = DATEADD(hh, -@hours, @date2);

SELECT @minutes = DATEDIFF(mi, @date1, @date2);

IF DATEADD(mi, -@minutes, @date2) < @date1
    SELECT @minutes = @minutes - 1;

SET @date2 = DATEADD(mi, -@minutes, @date2);

SELECT @seconds = DATEDIFF(s, @date1, @date2);

IF DATEADD(s, -@seconds, @date2) < @date1
    SELECT @seconds = @seconds - 1;

SET @date2 = DATEADD(s, -@seconds, @date2);

SELECT @milliseconds = DATEDIFF(ms, @date1, @date2);

SELECT @result = ISNULL(CAST (NULLIF (@years, 0) AS VARCHAR (10)) + ' years,', '')
    + ISNULL(' ' + CAST (NULLIF (@months, 0) AS VARCHAR (10)) + ' months,', '')
    + ISNULL(' ' + CAST (NULLIF (@days, 0) AS VARCHAR (10)) + ' days,', '')
    + ISNULL(' ' + CAST (NULLIF (@hours, 0) AS VARCHAR (10)) + ' hours,', '')
    + ISNULL(' ' + CAST (@minutes AS VARCHAR (10)) + ' minutes and', '')
    + ISNULL(' ' + CAST (@seconds AS VARCHAR (10)) + CASE
        WHEN @milliseconds > 0
        THEN '.' + CAST (@milliseconds AS VARCHAR (10))
        ELSE ''
    END + ' seconds', '');

SELECT @result;

Voici le jeu de résultats.

118 years, 11 months, 11 days, 7 hours, 8 minutes and 1.123 seconds

Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)

Ces exemples utilisent différents types d’expressions comme arguments pour les paramètres startdate et enddate.

J. Spécifier des colonnes pour startdate et enddate

Cet exemple calcule le nombre de limites de jour qui sont traversées entre les dates de deux colonnes dans une table.

CREATE TABLE dbo.Duration
(
    startDate DATETIME2,
    endDate DATETIME2
);

INSERT INTO dbo.Duration (startDate, endDate)
VALUES ('2007-05-06 12:10:09', '2007-05-07 12:10:09');

SELECT TOP (1) DATEDIFF(day, startDate, endDate) AS [Duration]
FROM dbo.Duration;

Duration
--------
1

K. Spécifier des sous-requêtes scalaires et des fonctions scalaires pour la date de début et la date de fin

Cet exemple utilise des sous-requêtes et des fonctions scalaires comme arguments pour startdate et enddate.

-- Uses AdventureWorks
SELECT TOP (1) DATEDIFF(day, (SELECT MIN(HireDate)
                              FROM dbo.DimEmployee), (SELECT MAX(HireDate)
                                                      FROM dbo.DimEmployee))
FROM dbo.DimEmployee;

L. Spécifier des constantes pour la date de début et la date de fin

Cet exemple suivant des constantes à caractères comme arguments pour startdate et enddate.

-- Uses AdventureWorks
SELECT TOP (1) DATEDIFF(day, '2007-05-07 09:53:01.0376635', '2007-05-08 09:53:01.0376635')
FROM DimCustomer;

M. Spécifier des fonctions de classement pour startdate

Cet exemple utilise une fonction de classement comme argument pour startdate.

-- Uses AdventureWorks
SELECT FirstName,
       LastName,
       DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY DepartmentName), SYSDATETIME()) AS RowNumber
FROM dbo.DimEmployee;

N. Spécifier une fonction de fenêtre d’agrégation pour startdate

Cet exemple utilise une fonction d’agrégation comme argument pour startdate.

-- Uses AdventureWorks
SELECT FirstName,
       LastName,
       DepartmentName,
       DATEDIFF(year, MAX(HireDate) OVER (PARTITION BY DepartmentName), SYSDATETIME()) AS SomeValue
FROM dbo.DimEmployee;

Contenu connexe