Condividi tramite

DATEDIFF (Transact-SQL)

  • Articolo

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Piattaforma di strumenti analitici (PDW)

Questa funzione restituisce il numero (sotto forma di valore intero con segno) di limiti del datepart specificato sovrapposti tra gli elementi startdate ed enddate indicati.

Vedere DATEDIFF_BIG per una funzione che gestisce differenze maggiori tra i valori startdate e enddate . Vedere Funzioni e tipi di dati di data e ora per una panoramica di tutti i tipi di dati e le funzioni di data e ora Transact-SQL.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

DATEDIFF ( datepart , startdate , enddate )

Argomenti

datepart

Specifica le unità in cui DATEDIFF indica la differenza tra startdate e enddate. Le unità datepart comunemente usate includono month o second.

Il valore datepart non può essere specificato in una variabile, né come stringa tra virgolette come 'month'.

Nella tabella seguente sono elencati tutti i valori datepart validi. DATEDIFF accetta il nome completo del datepart o qualsiasi abbreviazione elencata del nome completo.

Nome datepart Abbreviazione 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

Ogni nome e abbreviazione di datepart specifici per il nome datepart restituisce lo stesso valore.

startdate

Espressione che può risolversi in uno dei valori seguenti:

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

Per evitare ambiguità, esprimere gli anni nel formato a quattro cifre. Per informazioni sui valori dell'anno a due cifre, vedere Configurazione del server: cutoff anno a due cifre.

enddate

Vedere startdate.

Tipi restituiti

int

Valore restituito

La differenza int tra startdate ed enddate, espressa nel limite impostato da datepart.

Ad esempio, restituisce SELECT DATEDIFF(day, '2036-03-01', '2036-02-28'); -2, che indica che il 2036 deve essere un anno bisestile. Questo caso significa che se si inizia a startdate 2036-03-01e quindi si contano -2 i giorni, si raggiunge l'enddate di 2036-02-28.

Per un valore restituito esterno all'intervallo per int (da -2.147.483.648 a +2.147.483.647), DATEDIFF restituisce un errore. Per millisecond, la differenza massima tra startdate e enddate è di 24 giorni, 20 ore, 31 minuti e 23,647 secondi. Per second, la differenza massima è 68 anni, 19 giorni, 3 ore, 14 minuti e 7 secondi.

Se startdate e enddate vengono assegnati solo un valore di ora e datepart non è un datepart di ora, DATEDIFF restituisce 0.

Per calcolare il valore restituito, DATEDIFF usa il componente di differenza di fuso orario di startdate o enddate.

Poiché smalldatetime è accurato solo per il minuto, i secondi e i millisecondi vengono sempre impostati 0 su nel valore restituito quando startdate o enddate hanno un valore smalldatetime .

Se a una variabile di tipo data viene assegnato solo il valore dell'ora, DATEDIFF imposta il valore della parte mancante della data sul valore predefinito: 1900-01-01. Se a una variabile di tipo ora o data viene assegnato solo il valore della data, DATEDIFF imposta il valore della parte mancante dell'ora sul valore predefinito: 00:00:00. Se startdate ed enddate hanno rispettivamente solo la parte relativa all'ora o solo la parte relativa alla data, DATEDIFF imposta le parti mancanti sui rispettivi valori predefiniti.

Se startdate e enddate hanno tipi di dati di data diversi e uno ha più parti di tempo o precisione frazionarie di secondi rispetto all'altra, DATEDIFF imposta le parti mancanti dell'altro su 0.

Limiti di datepart

Le istruzioni seguenti hanno gli stessi valori startdate ed enddate. Queste date sono adiacenti e differiscono di cento nanosecondi (0,0000001 secondi). La differenza tra startdate e enddate in ogni istruzione oltrepassa un limite di calendario o di ora del rispettivo valore datepart. Ogni istruzione restituisce 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');

Se startdate e enddate hanno valori di anno diversi, ma hanno gli stessi valori della settimana del calendario, DATEDIFF restituisce 0 per datepart week.

Osservazioni:

Usare DATEDIFF nelle clausole , HAVINGWHERE, GROUP BY, e ORDER BY .SELECT <list>

DATEDIFF consente di eseguire in modo implicito il cast di valori letterali stringa come tipo di dati datetime2. In altre parole, DATEDIFF non supporta il formato YDM quando la data viene passata come stringa. Per usare il YDM formato, è necessario eseguire il cast esplicito della stringa in un tipo datetime o smalldatetime.

La specifica di SET DATEFIRST non ha alcun effetto su DATEDIFF. DATEDIFF usa sempre la domenica come primo giorno della settimana, per garantire che la funzione operi in modo deterministico.

DATEDIFFse la differenza tra enddate e startdate restituisce minute un valore non compreso nell'intervallo per int.

Esempi

Gli esempi di codice Transact-SQL in questo articolo utilizzano il database campione AdventureWorks2022 o AdventureWorksDW2022, che è possibile scaricare dalla home page di Esempi di Microsoft SQL Server e progetti collettivi.

Questi esempi usano tipi diversi di espressioni come argomenti per i parametri startdate ed enddate.

R. Specificare le colonne per startdate e enddate

Questo esempio calcola il numero di limiti di giorno sovrapposti tra le date di due colonne in una tabella.

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;

Il set di risultati è il seguente.

Duration
--------
1

B. Specificare le variabili definite dall'utente per startdate e enddate

In questo esempio, variabili definite dall'utente fungono da argomenti per startdate ed 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. Specificare le funzioni di sistema scalari per startdate e enddate

Questo esempio usa funzioni di sistema scalari come argomenti per startdate ed enddate.

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

D. Specificare sottoquery scalari e funzioni scalari per startdate ed enddate

Questo esempio usa sottoquery e funzioni scalari come argomenti per startdate ed enddate.

USE AdventureWorks2022;
GO

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

E. Specificare le costanti per startdate e enddate

Questo esempio usa costanti di tipo carattere come argomenti per startdate ed enddate.

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

F. Specificare espressioni numeriche e funzioni di sistema scalari per enddate

Questo esempio usa un'espressione numerica, (GETDATE() + 1), e funzioni di sistema scalari, GETDATE e SYSDATETIME, come argomenti per 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. Specificare le funzioni di classificazione per startdate

Questo esempio usa una funzione di rango come argomento per 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. Specificare una funzione finestra di aggregazione per startdate

Questo esempio usa una funzione finestra di aggregazione come argomento per un parametro 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. Trovare la differenza tra startdate e enddate come stringhe di parti di data

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

Il set di risultati è il seguente.

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

Esempi: Azure Synapse Analytics e Piattaforma di strumenti analitici (PDW)

Questi esempi usano tipi diversi di espressioni come argomenti per i parametri startdate ed enddate.

J. Specificare le colonne per startdate e enddate

Questo esempio calcola il numero di limiti di giorno sovrapposti tra le date di due colonne in una tabella.

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. Specificare sottoquery scalari e funzioni scalari per startdate ed enddate

Questo esempio usa sottoquery e funzioni scalari come argomenti per startdate ed enddate.

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

.L Specificare le costanti per startdate e enddate

Questo esempio usa costanti di tipo carattere come argomenti per startdate ed 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. Specificare le funzioni di classificazione per startdate

Questo esempio usa una funzione di rango come argomento per startdate.

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

N. Specificare una funzione finestra di aggregazione per startdate

Questo esempio usa una funzione finestra di aggregazione come argomento per un parametro startdate.

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

Contenuto correlato