DATEDIFF_BIG (Transact-SQL)

Gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL DatabaseAzure SQL Managed Instance

Den här funktionen returnerar antalet (som ett signerat stort heltalsvärde) för de angivna datepart-gränserna som korsas mellan angivet startdatum och slutdatum.

Se Datatyper och funktioner för datum och tid (Transact-SQL) för en översikt över alla datatyper och funktioner för Transact-SQL-datum och tid.

Transact-SQL syntaxkonventioner

Syntax

DATEDIFF_BIG ( datepart , startdate , enddate )  

Arguments

datepart
Den del av startdate och enddate som anger vilken typ av gräns som korsas.

I den här tabellen visas alla giltiga datepart-argumentnamn och förkortningar.

startdate
Ett uttryck som kan matcha något av följande värden:

• date
• datetime
• datetimeoffset
• datetime2
• smalldatetime
• time

För datumDATEDIFF_BIG accepterar ett kolumnuttryck, uttryck, strängliteral eller användardefinierad variabel. Ett strängliteralt värde måste matchas till en datetime. Använd fyrsiffriga år för att undvika tvetydighetsproblem. DATEDIFF_BIG subtraherar startdate från enddate. Använd fyrsiffriga år för att undvika tvetydighet. Mer information om tvåsiffriga år finns i Konfigurera det tvåsiffriga alternativet för serverkonfiguration .

enddate
Se startdate.

Returtyp

Signerad bigint

Returvärde

Returnerar den stora skillnaden mellan startdate och enddate, uttryckt i gränsen som anges av datepart.

Ett fel returneras för ett returvärde som ligger utom intervallet för bigint (-9 223 372 036 854 775 808 till 9 223 372 036 854 775 807 DATEDIFF_BIG ). Till skillnad från , som returnerar en int och därför kan spilla över en minut eller högre, DATEDIFF_BIG kan bara spillas om du använder nanosekunder precision där skillnaden mellan enddate och startdate är mer än 292 år, 3 månader, 10 dagar, 23 timmar, 47 minuter och 16,8547758 sekunder.

Om både startdatum och slutdatum endast tilldelas ett tidsvärde och datumdelen inte är en tidsdatumdelDATEDIFF_BIG returnerar 0.

DATEDIFF_BIG använder en tidszonsförskjutningskomponent i startdate eller enddate för att beräkna returvärdet.

För ett smalldatetime-värde som används för startdate eller enddateDATEDIFF_BIG anger alltid sekunder och millisekunder till 0 i returvärdet eftersom smalldatetime bara har noggrannhet till minuten.

Om endast ett tidsvärde tilldelas till en variabel DATEDIFF_BIG för datumdatatyp anger du värdet för den saknade datumdelen till standardvärdet: 1900-01-01. Om endast ett datumvärde tilldelas till en variabel av en tids- eller datumdatatyp anger DATEDIFF_BIG du värdet för den saknade tidsdelen till standardvärdet: 00:00:00. Om antingen startdate eller enddate bara har en tidsdel och den andra endast en datumdel, DATEDIFF_BIG anger du de saknade tids- och datumdelarna till standardvärdena.

Om startdate och enddate har olika datumdatatyper och den ena har mer precision för tidsdelar eller bråksekunder än den andra anger DATEDIFF_BIG de saknade delarna av den andra till 0.

datepart-gränser

Följande instruktioner har samma startdatum och samma enddate-värden . Dessa datum ligger intill varandra och de skiljer sig i tid med hundra nanosekunder (.0000001 sekund). Skillnaden mellan startdate och enddate i varje -instruktion korsar en kalender eller tidsgräns för dess datepart. Varje instruktion returnerar 1. Om startdatum och slutdatum har olika årsvärden men de har samma kalenderveckasvärden DATEDIFF_BIG returneras 0 för datepartweek.

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

Anmärkningar

Använd DATEDIFF_BIG i satserna SELECT <list>, WHERE, GROUP BYHAVINGoch ORDER BY .

DATEDIFF_BIG omvandlar implicit strängliteraler som en datetime2-typ . Det innebär att DATEDIFF_BIG det inte stöder formatet YDM när datumet skickas som en sträng. Du måste uttryckligen omvandla strängen till en datetime - eller smalldatetime-typ för att använda YDM-formatet.

Att ange SET DATEFIRST har ingen effekt på DATEDIFF_BIG. DATEDIFF_BIG använder alltid söndag som den första dagen i veckan för att säkerställa att funktionen fungerar på ett deterministiskt sätt.

DATEDIFF_BIG kan flöda över med en nanosekunder om skillnaden mellan enddate och startdate returnerar ett värde som ligger utom räckhåll för bigint.

Examples

Ange kolumner för startdate och enddate

I det här exemplet används olika typer av uttryck som argument för parametrarna startdate och enddate . Den beräknar antalet daggränser som korsas mellan datum i två kolumner i en tabell.

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  

Hitta skillnaden mellan startdate och enddate som datumdelssträngar

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

Här är resultatet.

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

Se närmare relaterade exempel i DATEDIFF (Transact-SQL).

Se även

CAST och CONVERT (Transact-SQL)
DATEDIFF (Transact-SQL)