DATEDIFF (Transact-SQL)

Artikel
04/26/2023

Gilt für:SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Diese Funktion gibt die Anzahl (ganze Zahl mit Vorzeichen) der angegebenen „datepart“-Begrenzungen zurück, die zwischen den angegebenen Werten für startdate und enddate überschritten wurden.

Unter DATEDIFF_BIG (Transact-SQL) finden Sie eine Funktion, die größere Unterschiede zwischen den startdate- und enddate-Werten behandelt. Eine Übersicht über alle Datums- und Uhrzeitdatentypen und zugehörigen Funktionen für Transact-SQL finden Sie unter Datums- und Uhrzeitdatentypen und zugehörige Funktionen (Transact-SQL).

Transact-SQL-Syntaxkonventionen

Syntax

DATEDIFF ( datepart , startdate , enddate )

Hinweis

Informationen zum Anzeigen der Transact-SQL-Syntax für SQL Server 2014 oder früher finden Sie unter Dokumentation zu früheren Versionen.

Argumente

datepart
Die Einheiten, in denen DATEDIFF die Differenz zwischen dem Startdatum und Enddatum meldet. Häufig verwendete datepart-Einheiten sind month oder second.

Der datepart-Wert kann nicht in einer Variablen angegeben werden und auch nicht als eine Zeichenfolge in Anführungszeichen wie 'month'.

In der folgenden Tabelle werden alle gültigen datepart-Werte aufgeführt. DATEDIFF nimmt entweder den vollständigen Namen von datepart oder eine beliebige aufgelistete Abkürzung des vollständigen Namens an.

datepart-Name	datepart-Abkürzung
year	y, yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

Hinweis

Alle spezifischen datepart-Namen und Abkürzungen für diesen datepart-Namen geben den gleichen Wert zurück.

startdate
Ein Ausdruck, der in einen der folgenden Werte aufgelöst werden kann:

date
datetime
datetimeoffset
datetime2
smalldatetime
time

Um Mehrdeutigkeiten zu vermeiden, sollten Sie vierstellige Jahreszahlen verwenden. Informationen zu zweistelligen Jahreszahlen finden Sie unter Konfigurieren der Serverkonfigurationsoption „Umstellungsjahr für Angaben mit zwei Ziffern“.

enddate
Weitere Informationen finden Sie unter startdate.

Rückgabetyp

int

Rückgabewert

Die int-Differenz zwischen startdate und enddate, ausgedrückt in der durch datepart festgelegten Grenze.

Beispielsweise gibt SELECT DATEDIFF(day, '2036-03-01', '2036-02-28'); -2 zurück, was darauf hindeutet, dass 2036 ein Schaltjahr sein muss. Dieser Fall bedeutet Folgendes: Wenn wir mit startdate „2036-03-01-01“ beginnen und dann -2 Tage zählen, erreichen wir das enddate „2036-02-28“.

Bei einem Rückgabewert, der sich außerhalb des gültigen Bereichs für int (-2,147,483,648 bis + 2,147,483,647) befindet, gibt DATEDIFF einen Fehler zurück. Der maximale Unterschied zwischen startdate und enddate beträgt für millisecond 24 Tage, 20 Stunden, 31 Minuten und 23,647 Sekunden. Für second beträgt der maximale Unterschied 68 Jahre, 19 Tage, 3 Stunden, 14 Minuten und 7 Sekunden.

Wenn startdate und enddate jeweils nur ein Uhrzeitwert zugewiesen ist und datepart kein Zeit-datepart ist, gibt DATEDIFF 0 (null) zurück.

DATEDIFF verwendet die Zeitzonen-Offsetkomponente von startdate oder enddate, um den Rückgabewert zu berechnen.

Da smalldatetime nur auf die Minute genaue Werte zurückgibt, werden im Rückgabewert die Sekunden und Millisekunden immer auf 0 (null) festgelegt, wenn startdate oder enddate einen smalldatetime-Wert aufweist.

Wenn der Variablen eines Datumsdatentyps nur ein Uhrzeitwert zugewiesen ist, legt DATEDIFF den Wert des fehlenden Datumsteils auf den Standardwert 1900-01-01 fest. Wenn der Variablen eines Uhrzeit- oder Datumsdatentyps nur ein Datumswert zugewiesen ist, legt DATEDIFF den Wert des fehlenden Uhrzeitteils auf den Standardwert 00:00:00 fest. Wenn entweder startdate oder enddate nur über einen Uhrzeitteil und der andere nur über einen Datumsteil verfügt, legt DATEDIFF für die fehlenden Uhrzeit- und Datumstypen die Standardwerte fest.

Wenn startdate und enddate unterschiedliche Datumsdatentypen aufweisen und ein Datentyp mehr Uhrzeitteile oder eine höhere Genauigkeit bezüglich der Bruchteile von Sekunden aufweist als der andere Teil, legt DATEDIFF für die fehlenden Teile des anderen Datentyps 0 (null) fest.

datepart-Begrenzungen

Die folgenden Anweisungen weisen bei startdate und enddate den gleichen Wert auf. Die Datumsangaben folgen aufeinander und unterscheiden sich in der Uhrzeit um hundert Nanosekunden (0,0000001 Sekunde). Der Unterschied zwischen startdate und enddate in jeder Anweisung überschreitet eine Kalender- oder Uhrzeitbegrenzung des zugehörigen datepart. Jede Anweisung gibt 1 zurück.

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(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');

Wenn startdate und enddate unterschiedliche Jahreswerte aufweisen, die Kalenderwochenwerte jedoch identisch sind, gibt DATEDIFF für den datepart-Wert week 0 (null) zurück.

Bemerkungen

Verwenden Sie DATEDIFF in den Klauseln SELECT <list>, WHERE, HAVING, GROUP BY und ORDER BY.

DATEDIFF wandelt Zeichenfolgenliterale implizit in den datetime2-Typ um. Daher unterstützt DATEDIFF das Format YDM nicht, wenn das Datum als Zeichenfolge übergeben wird. Sie müssen die Zeichenfolge explizit in den Typ datetime oder smalldatetime umwandeln, um das YDM-Format zu verwenden.

Das Festlegen von SET DATEFIRST wirkt sich nicht auf DATEDIFF aus. DATEDIFF verwendet immer Sonntag als ersten Wochentag, um sicherzustellen, dass die Funktion deterministisch ist.

DATEDIFF kann mit der Genauigkeit minute oder mehr überlaufen, wenn der Unterschied zwischen enddate und startdate einen Wert zurückgibt, der sich außerhalb des Bereichs von int befindet.

Beispiele

In diesen Beispielen werden verschiedene Typen von Ausdrücken als Argumente für die Parameter startdate und enddate verwendet.

A. Angeben von Spalten für startdate und enddate

In diesem Beispiel wird die Anzahl der Tagesbegrenzungen berechnet, die von den Datumsangaben in zwei Spalten in einer Tabelle überschritten wurden.

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. Angeben von benutzerdefinierten Variablen für startdate und enddate

In diesem Beispiel werden benutzerdefinierte Variablen als Argumente für startdate und enddate verwendet.

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. Angeben von skalaren Systemfunktionen für startdate und enddate

In diesem Beispiel werden skalare Systemfunktionen als Argumente für startdate und enddate verwendet.

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

D: Angeben von skalaren Unterabfragen und skalaren Funktionen für startdate und enddate

In diesem Beispiel werden skalare Unterabfragen und skalare Funktionen als Argumente für startdate und enddate verwendet.

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

E. Angeben von Konstanten für startdate und enddate

In diesem Beispiel werden Zeichenkonstanten als Argumente für startdate und enddate verwendet.

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

F. Angeben von numerischen Ausdrücken und skalaren Systemfunktionen für enddate

In diesem Beispiel werden ein numerischer Ausdruck, (GETDATE() + 1), und skalare Systemfunktionen, GETDATE und SYSDATETIME, als Argumente für enddate verwendet.

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

G. Angeben von Rangfolgefunktionen für startdate

In diesem Beispiel wird eine Rangfolgefunktion als Argument für startdate verwendet.

USE AdventureWorks2012;  
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. Angeben einer Aggregatfensterfunktion für startdate

In diesem Beispiel wird eine Aggregatfensterfunktion als Argument für startdate verwendet.

USE AdventureWorks2012;  
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. Ermitteln des Unterschieds zwischen „startdate“ und „enddate“ als Zeichenfolgen für Datumsteile

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

Hier ist das Resultset.

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

Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)