DATEDIFF (Transact-SQL)

Articolo
04/26/2023

Si applica a:SQL Server database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Analytics Platform System (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 di sintassi Transact-SQL

Sintassi

DATEDIFF ( datepart , startdate , enddate )

Nota

Per visualizzare la sintassi Transact-SQL per SQL Server 2014 (12.x) e versioni precedenti, vedere la documentazione delle versioni precedenti.

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

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)