DATEDIFF_BIG (Transact-SQL)
Gilt für: 
SQL Server 2016 (13.x) und höher Azure SQL-Datenbank Azure SQL Managed Instance
Diese Funktion gibt die Anzahl (große ganze Zahl mit Vorzeichen) der angegebenen datepart-Begrenzungen zurück, die zwischen den angegebenen Werten für startdate und enddate überschritten wurden.
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_BIG ( datepart , startdate , enddate )
Argumente
datepart
Der Teil von startdate und enddate, der den Typ der überschrittenen Begrenzung angibt.
Hinweis
DATEDIFF_BIG akzeptiert keine datepart-Werte von benutzerdefinierten Variablen oder als Zeichenfolgen in Anführungszeichen.
In der folgenden Tabelle sind alle gültigen Namen und Abkürzungen für datepart-Argumente aufgeführt.
| datepart-Name | datepart-Abkürzung |
|---|---|
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| 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
Bei date akzeptiert DATEDIFF_BIG einen Spaltenausdruck, einen Ausdruck, ein Zeichenfolgenliteral oder eine benutzerdefinierte Variable. Ein Zeichenfolgenliteralwert muss in ein datetime-Argument aufgelöst werden. Um Mehrdeutigkeiten zu vermeiden, sollten Sie vierstellige Jahreszahlen verwenden. DATEDIFF_BIG subtrahiert startdate von enddate. Um Mehrdeutigkeiten zu vermeiden, sollten Sie vierstellige Jahreszahlen verwenden. Unter Konfigurieren der Serverkonfigurationsoption „Umstellungsjahr für Angaben mit zwei Ziffern“ finden Sie weitere Informationen zu zweistelligen Jahreszahlen.
enddate
Weitere Informationen finden Sie unter startdate.
Rückgabetyp
Signierter bigint-Wert
Rückgabewert
Hierbei wird der bigint-Unterschied zwischen den Werten für startdate und enddate, ausgedrückt in dem durch datepart festgelegten Grenzwert, zurückgegeben.
Bei einem Rückgabewert, der sich außerhalb des gültigen Bereichs für bigint (-9,223,372,036,854,775,808 bis 9,223,372,036,854,775,807) befindet, gibt DATEDIFF_BIG einen Fehler zurück. In einem anderen Fall hingegen wird ein int-Wert zurückgegeben. Aus diesem Grund kann ein minute- oder ein höherer Wert zu einem Überlauf führen. In DATEDIFF_BIG kann ein Überlauf auftreten, wenn die Genauigkeit nanosecond verwendet wird und der Unterschied zwischen enddate und startdate 292 Jahre, 3 Monate, 10 Tage, 23 Stunden 47 Minuten und 16,8547758 Sekunden überschreitet.
Wenn startdate und enddate jeweils nur ein Uhrzeitwert zugewiesen ist und datepart keine Zeit-datepart-Funktion ist, gibt DATEDIFF_BIG 0 zurück.
Beim Berechnen des Rückgabewerts verwendet DATEDIFF_BIG eine Komponente von startdate oder enddate für den Zeitzonenoffset.
Bei einem smalldatetime-Wert, der für startdate oder enddate verwendet wird, legt DATEDIFF_BIG die Sekunden und Millisekunden im Rückgabewert immer auf 0 (null) fest, da smalldatetime nur auf die Minute genaue Werte enthält.
Wenn der Variablen eines Datumsdatentyps nur ein Uhrzeitwert zugewiesen ist, legt DATEDIFF_BIG 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_BIG 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_BIG 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_BIG 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. Wenn startdate und enddate unterschiedliche Jahreswerte aufweisen, die Kalenderwochenwerte jedoch identisch sind, gibt DATEDIFF_BIG für den datepart-Wert week 0 (null) zurück.
SELECT DATEDIFF_BIG(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
Hinweise
Verwenden Sie DATEDIFF_BIG in den Klauseln SELECT <list>, WHERE, HAVING, GROUP BY und ORDER BY.
DATEDIFF_BIG wandelt Zeichenfolgenliterale implizit in den datetime2-Typ um. Daher unterstützt DATEDIFF_BIG 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_BIG aus. DATEDIFF_BIG verwendet immer Sonntag als ersten Wochentag, um sicherzustellen, dass die Funktion deterministisch ist.
In DATEDIFF_BIG kann bei einem nanosecond-Wert ein Überlauf auftreten, wenn der Unterschied zwischen enddate und startdate einen Wert zurückgibt, der sich außerhalb des Bereichs von bigint befindet.
Beispiele
Angeben von Spalten für startdate und enddate
In diesem Beispiel werden verschiedene Typen von Ausdrücken als Argumente für die Parameter startdate und enddate verwendet. Die Anzahl der Tagesbegrenzungen wird 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_BIG(day, startDate, endDate) AS 'Duration'
FROM dbo.Duration;
-- Returns: 1
Ermitteln des Unterschieds zwischen „startdate“ und „enddate“ als Zeichenfolgen für Datumsteile
DECLARE @date1 DATETIME2, @date2 DATETIME2, @result VARCHAR(100)
DECLARE @years BIGINT, @months BIGINT, @days BIGINT, @hours BIGINT, @minutes BIGINT, @seconds BIGINT, @milliseconds BIGINT
SET @date1 = '0001-01-01 00:00:00.00000000'
SET @date2 = '2018-12-12 07:08:01.12345678'
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.
2017 years, 11 months, 11 days, 7 hours, 8 minutes and 1.123 seconds
Weitere vergleichbare Beispiele finden Sie unter DATEDIFF (Transact-SQL).