DATEDIFF (Transact-SQL)
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 (Transact-SQL) per una funzione che gestisca differenze maggiori tra i valori startdate ed enddate. Per una panoramica di tutti i tipi di dati e funzioni di data e ora Transact-SQL, vedere Funzioni e tipi di dati di data e ora (Transact-SQL).
Convenzioni relative alla sintassi Transact-SQL
Sintassi
DATEDIFF ( datepart , startdate , enddate )
Argomenti
datepart
Unità usata da DATEDIFF per indicare la differenza tra startdate ed enddate. Le unità datepart comunemente usate includono month
o second
.
Non è possibile specificare il valore datepart 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 o datepart o qualsiasi abbreviazione elencata del nome completo.
Nome datepart | Abbreviazione datepart |
---|---|
year | y, yy, yyyy |
quarter | qq, q |
month | mm, m |
dayofyear | dy |
day | dd, d |
week | wk, ww |
weekday | dw, w |
hour | hh |
minute | mi, n |
second | ss, s |
millisecond | ms |
microsecond | mcs |
nanosecond | ns |
Nota
Ogni nome datepart specifico e le abbreviazioni datepart corrispondenti restituiscono 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 relativi agli anni a due cifre, vedere Configurare l'opzione di configurazione del server Cambio data per anno a due cifre.
enddate
Vedere startdate.
Tipo restituito
int
Valore restituito
La differenza int tra startdate ed enddate, espressa nel limite impostato da datepart.
Ad esempio, SELECT DATEDIFF(day, '2036-03-01', '2036-02-28');
restituisce -2, a indicare che il 2036 deve essere un anno bisestile. Questo caso significa che se si inizia da startdate '2036-03-01' e quindi si sottraggono 2 giorni, enddate sarà '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 è 24 giorni, 20 ore, 31 minuti e 23,647 secondi. Per second, la differenza massima è di 68 anni, 19 giorni, 3 ore, 14 minuti e 7 secondi.
Se sia a startdate che a enddate è stato assegnato solo un valore orario e datepart non è un datepart orario, DATEDIFF
restituisce 0.
Per calcolare il valore restituito, DATEDIFF
usa il componente di differenza di fuso orario di startdate o enddate.
Dal momento che smalldatetime garantisce la precisione solo a livello di minuti, i secondi e i millisecondi vengono sempre impostati su 0 nel valore restituito quando startdate o enddate contiene 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 i valori startdate ed enddate sono di tipi data diversi e uno di questi comprende un numero maggiore di parti di ora o offre una precisione in secondi frazionari maggiore, DATEDIFF
imposta le parti mancanti dell'altro valore 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. Ciascuna 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 ed enddate hanno valori di anno diversi, ma gli stessi valori di settimana di calendario, DATEDIFF
restituirà 0 per datepart week.
Osservazioni:
Usare DATEDIFF
nelle clausole SELECT <list>
, WHERE
, HAVING
, GROUP BY
e ORDER BY
.
DATEDIFF
consente di eseguire in modo implicito il cast di valori letterali stringa come tipo di dati datetime2. DATEDIFF
, pertanto, non supporta il formato AGM se la data viene passata come stringa. Per usare il formato AGM è necessario eseguire il cast della stringa in modo esplicito 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.
DATEDIFF
può determinare un overflow con una precisione di minute o superiore se la differenza tra enddate e startdate restituisce un valore non compreso nell'intervallo per int.
Esempi
Questi esempi usano tipi diversi di espressioni come argomenti per i parametri startdate ed enddate.
R. Specifica di colonne per startdate ed 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;
-- Returns: 1
B. Specifica di variabili definite dall'utente per startdate ed enddate
In questo esempio, variabili definite dall'utente fungono da argomenti per startdate ed enddate.
DECLARE @startdate DATETIME2 = '2007-05-05 12:10:09.3312722';
DECLARE @enddate DATETIME2 = '2007-05-04 12:10:09.3312722';
SELECT DATEDIFF(day, @startdate, @enddate);
C. Specifica di funzioni di sistema scalari per startdate ed enddate
Questo esempio usa funzioni di sistema scalari come argomenti per startdate ed enddate.
SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME());
D. Specifica di sottoquery scalari e di 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. Specifica di costanti per startdate ed 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. Specifica di 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. Specifica di funzioni di rango 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 s
INNER JOIN Person.Person p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
H. Specifica di 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 sod
INNER JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.SalesOrderID IN(43659, 58918);
GO
I. Ricerca della differenza tra startdate e enddate come stringhe di parti di data
-- DOES NOT ACCOUNT FOR LEAP YEARS
DECLARE @date1 DATETIME, @date2 DATETIME, @result VARCHAR(100);
DECLARE @years INT, @months INT, @days INT,
@hours INT, @minutes INT, @seconds INT, @milliseconds 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
Questo è il set di risultati.
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. Specifica di colonne per startdate ed 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;
-- Returns: 1
K. Specifica di sottoquery scalari e di 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 Specifica di costanti per startdate ed 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. Specifica di funzioni di rango 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. Specifica di 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