DATEDIFF (Transact-SQL)

Artikel
10/29/2024

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.

Siehe DATEDIFF_BIG für eine Funktion, die größere Unterschiede zwischen den Anfangs- und Enddatumswerten behandelt. Eine Übersicht über alle Transact-SQL-Datums- und Uhrzeitdatentypen und -funktionen finden Sie unter Datums- und Uhrzeitdatentypen und -funktionen .

Transact-SQL-Syntaxkonventionen

Syntax

DATEDIFF ( datepart , startdate , enddate )

Argumente

datepart

Gibt die Einheiten an, in denen DATEDIFF der Unterschied zwischen Startdatum und Enddatum gemeldet wird. Häufig verwendete datepart-Einheiten sind month oder second.

Der Datepart-Wert kann nicht in einer Variablen angegeben werden, noch als an zitierte Zeichenfolge wie 'month'.

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

datepart-Name	datepart-Abkürzung
`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`

Jeder bestimmte Datumsteilname und abkürzungen für diesen Datepart-Namen gibt denselben 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 Jahreswerten finden Sie unter Serverkonfiguration: zweistellige Jahreskürzung .

enddate

Weitere Informationen finden Sie unter startdate.

Rückgabetypen

int

Rückgabewert

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

Gibt beispielsweise zurück, was darauf hinweist-2, SELECT DATEDIFF(day, '2036-03-01', '2036-02-28'); dass 2036 ein Schaltjahr sein muss. Dieser Fall bedeutet, dass, wenn wir am Anfangsdatum 2036-03-01 beginnen und dann Tage zählen-2, wir das Enddatum von 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 millisecondmaximale Unterschied zwischen Startdatum und Enddatum beträgt 24 Tage, 20 Stunden, 31 Minuten und 23,647 Sekunden. Der secondmaximale Unterschied beträgt 68 Jahre, 19 Tage, 3 Stunden, 14 Minuten und 7 Sekunden.

Wenn Startdatum und Enddatum nur einem Zeitwert zugewiesen sind und das Datumsteil kein Uhrzeitdatumsteil ist, DATEDIFF wird zurückgegeben.0

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

Da smalldatetime nur auf die Minute genau ist, werden Sekunden und Millisekunden immer im Rückgabewert festgelegt 0 , wenn Startdatum oder Enddatum einen Smalldatetime-Wert aufweisen.

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 "Startdatum " und "Enddate " unterschiedliche Datumsdatentypen aufweisen und eine mit mehr Zeitteilen oder Bruch sekundengenauigkeit als der andere verwendet wird, DATEDIFF werden die fehlenden Teile des anderen werts festgelegt 0.

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 zurück 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');

Wenn "Startdatum" und "Enddatum" unterschiedliche Jahreswerte aufweisen, aber dieselben Kalenderwochenwerte aufweisen, DATEDIFF wird für "datepartweek" zurückgegeben0.

Hinweise

Verwenden Sie DATEDIFF diese Klauseln in den SELECT <list>Klauseln , WHERE, , HAVINGund GROUP BYORDER BY

DATEDIFF wandelt Zeichenfolgenliterale implizit in den datetime2-Typ um. Anders ausgedrückt, wird das Format YDM nicht unterstützt, DATEDIFF wenn das Datum als Zeichenfolge übergeben wird. Sie müssen die Zeichenfolge explizit in einen Datetime - oder Smalldatetime-Typ 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 einer Genauigkeit von minute oder höher überlaufen, wenn der Unterschied zwischen Enddatum und Startdatum einen Wert zurückgibt, der außerhalb des Bereichs für int liegt.

Beispiele

Die Transact-SQL-Codebeispiele in diesem Artikel verwenden die AdventureWorks2022- oder AdventureWorksDW2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.

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 Startdatum und Enddatum

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;

Hier sehen Sie das Ergebnis.

Duration
--------
1

B. Angeben von benutzerdefinierten Variablen für Startdatum und Enddatum

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

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. Angeben skalarer Systemfunktionen für Startdatum und Enddatum

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

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

D: Angeben skalarer Unterabfragen und Skalarfunktionen für Startdatum und Enddatum

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

USE AdventureWorks2022;
GO

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

E. Angeben von Konstanten für Startdatum und Enddatum

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 numerischer Ausdrücke und skalarer 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 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. Angeben von Bewertungsfunktionen für startdate

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

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. Angeben einer Aggregatfensterfunktion für das Startdatum

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

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. Ermitteln der Differenz zwischen Anfangs- und Enddatum als Zeichenfolgen für Datumsteile

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

Hier sehen Sie das Ergebnis.

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

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

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

J. Angeben von Spalten für Startdatum und Enddatum

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 TOP (1) DATEDIFF(day, startDate, endDate) AS [Duration]
FROM dbo.Duration;

Duration
--------
1

K. Angeben skalarer Unterabfragen und Skalarfunktionen für Startdatum und Enddatum

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

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

L. Angeben von Konstanten für Startdatum und Enddatum

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

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

M. Angeben von Bewertungsfunktionen für startdate

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

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

N. Angeben einer Aggregatfensterfunktion für das Startdatum

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

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

Freigeben über

DATEDIFF (Transact-SQL)

Syntax

Argumente

datepart

startdate

enddate

Rückgabetypen

Rückgabewert

datepart-Begrenzungen

Hinweise

Beispiele

A. Angeben von Spalten für Startdatum und Enddatum

B. Angeben von benutzerdefinierten Variablen für Startdatum und Enddatum

C. Angeben skalarer Systemfunktionen für Startdatum und Enddatum

D: Angeben skalarer Unterabfragen und Skalarfunktionen für Startdatum und Enddatum

E. Angeben von Konstanten für Startdatum und Enddatum

F. Angeben numerischer Ausdrücke und skalarer Systemfunktionen für Enddate

G. Angeben von Bewertungsfunktionen für startdate

H. Angeben einer Aggregatfensterfunktion für das Startdatum

I. Ermitteln der Differenz zwischen Anfangs- und Enddatum als Zeichenfolgen für Datumsteile

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

J. Angeben von Spalten für Startdatum und Enddatum

K. Angeben skalarer Unterabfragen und Skalarfunktionen für Startdatum und Enddatum

L. Angeben von Konstanten für Startdatum und Enddatum

M. Angeben von Bewertungsfunktionen für startdate

N. Angeben einer Aggregatfensterfunktion für das Startdatum

Feedback

Zusätzliche Ressourcen

Freigeben über

DATEDIFF (Transact-SQL)

Syntax

Argumente

datepart

startdate

enddate

Rückgabetypen

Rückgabewert

datepart-Begrenzungen

Hinweise

Beispiele

A. Angeben von Spalten für Startdatum und Enddatum

B. Angeben von benutzerdefinierten Variablen für Startdatum und Enddatum

C. Angeben skalarer Systemfunktionen für Startdatum und Enddatum

D: Angeben skalarer Unterabfragen und Skalarfunktionen für Startdatum und Enddatum

E. Angeben von Konstanten für Startdatum und Enddatum

F. Angeben numerischer Ausdrücke und skalarer Systemfunktionen für Enddate

G. Angeben von Bewertungsfunktionen für startdate

H. Angeben einer Aggregatfensterfunktion für das Startdatum

I. Ermitteln der Differenz zwischen Anfangs- und Enddatum als Zeichenfolgen für Datumsteile

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

J. Angeben von Spalten für Startdatum und Enddatum

K. Angeben skalarer Unterabfragen und Skalarfunktionen für Startdatum und Enddatum

L. Angeben von Konstanten für Startdatum und Enddatum

M. Angeben von Bewertungsfunktionen für startdate

N. Angeben einer Aggregatfensterfunktion für das Startdatum

Zugehöriger Inhalt

Feedback

Zusätzliche Ressourcen