DATEDIFF (Transact-SQL)

發行項
04/26/2023

適用於：SQL Server Azure SQL Database Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW)

此函式會傳回跨越指定 startdate 和 enddate 之指定 datepart 界限的計數 (作為帶正負號的整數值)。

如需處理 startdate 與 enddate 值之間較大差異的函式，請參閱 DATEDIFF_BIG (Transact-SQL)。如需所有 Transact-SQL 日期和時間資料類型與函式的概觀，請參閱日期和時間資料類型與函式 (Transact-SQL)。

Transact-SQL 語法慣例

Syntax

DATEDIFF ( datepart , startdate , enddate )

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法，請參閱舊版文件。

引數

datepart
DATEDIFF 所報告 startdate 和 enddate 之間差異的單位。常用的 datepart 單位包括 month 或 second。

Datepart 值不能在變數中指定，也不能是以引號括住的字串 (例如 'month')。

下表列出所有有效的 datepart 值。 DATEDIFF 接受 datepart 的完整名稱，或任何所列出的完整名稱縮寫。

datepart 名稱	datepart 縮寫
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

注意

每個特定 datepart 名稱和該 datepart 名稱的縮寫都會傳回相同值。

startdate
可解析成下列其中一個值的運算式：

date
datetime
datetimeoffset
datetime2
smalldatetime
time

請使用四位數年份以避免模糊不清。如需兩位數年份值的資訊，請參閱設定兩位數年份的截止伺服器設定選項。

enddate
請參閱＜startdate＞。

傳回類型

int

傳回值

startdate 和 enddate 之間的 int 差異，以 datepart 所設定的界限表示。

例如，SELECT DATEDIFF(day, '2036-03-01', '2036-02-28'); 會傳回-2，提示 2036 必須是閏年。這種情況表示如果我們從 startdate '2036-03-01' 開始，然後計數 -2 天，則 enddate 會是 '2036-02-28'。

針對超出 int 範圍 (-2,147,483,648 到 +2,147,483,647) 的傳回值，DATEDIFF 會傳回錯誤。針對 millisecond，startdate 和 enddate 最大的差異為 24 天 20 小時 31 分鐘 23.647 秒。針對 second，最大的差異為 68 年 19 天 3 小時 14 分鐘 7 秒。

如果 startdate 和 enddate 都只獲指派時間值，且 datepart 不是時間 datepart，DATEDIFF 會傳回 0。

DATEDIFF 會使用 startdate 或 enddate 的時區時差元件來計算傳回值。

由於 smalldatetime 的精確度只有到分鐘，因此當 startdate 或 enddate 具有 smalldatetime 值時，秒和毫秒就一律會在傳回值中設定為 0。

如果您只有將時間值指派給日期資料類型變數，DATEDIFF 會將遺漏日期部分的值設定為預設值：1900-01-01。如果您只有將日期值指派給時間或日期資料類型的變數，DATEDIFF 會將遺漏時間部分的值設定為預設值：00:00:00。如果 startdate 或 enddate 其中之一只有時間部分，而另一個只有日期部分，DATEDIFF 會將遺漏的時間和日期部分設定為預設值。

如果 startdate 和 enddate 具有不同的日期資料類型，而且其中一個項目的時間部分或小數秒數有效位數超過另一個項目，DATEDIFF 會將另一個項目的遺漏部分設定為 0。

datepart 界限

下列陳述式具有相同的 startdate 和相同的 enddate 值。這些日期都很接近且時間差距為一百奈秒 (.0000001 秒)。每個陳述式中 startdate 與 enddate 之間的差異會跨越其 datepart 的日曆或時間界限。每個陳述式都會傳回 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(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');

如果 startdate 和 enddate 具有不同年份值，但具有相同日曆週值，則 DATEDIFF 會針對 datepartweek 傳回 0。

備註

您可以在 SELECT <list>、WHERE、HAVING、GROUP BY 和 ORDER BY 子句中使用 DATEDIFF。

DATEDIFF 會以隱含的方式，將字串常值轉換為 datetime2 類型。這表示，將日期當作字串傳遞時，DATEDIFF 不支援 YDM 格式。您必須明確地將字串轉換為 datetime 或 smalldatetime 類型，才能使用 YDM 格式。

指定 SET DATEFIRST 對 DATEDIFF 沒有任何作用。 DATEDIFF 一律會使用星期天當作一週的第一天，以確保此函式以具決定性的方式運作。

如果 enddate 與 startdate 的差距傳回超出 int 範圍的值，則 DATEDIFF 可使用 minute 或更高的精確度進行溢位。

範例

這些範例會使用不同的運算式類型，當作 startdate 和 enddate 參數的引數。

A. 指定 startdate 和 enddate 的資料行

此範例會計算資料表的兩個資料行日期之間跨越界限的天數。

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. 指定 startdate 和 enddate 的使用者自訂變數

在此範例中，會以使用者定義的變數作為 startdate 和 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. 指定 startdate 和 enddate 的純量系統函數

此範例會使用純量系統函數，當作 startdate 和 enddate 的引數。

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

D. 指定 startdate 和 enddate 的純量子查詢和純量函數

此範例會使用純量子查詢和純量函數，當作 startdate 和 enddate 的引數。

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

E. 指定 startdate 和 enddate 的常數

此範例會使用字元常數，當作 startdate 和 enddate 的引數。

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

F. 指定 enddate 的數值運算式和純量系統函數

此範例會使用數值運算式 (GETDATE() + 1) 和純量系統函數 GETDATE 與 SYSDATETIME，當作 enddate 的引數。

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. 指定 startdate 的排名函數

此範例會使用次序函數，當作 startdate 的引數。

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. 指定 startdate 的彙總視窗函數

此範例會使用彙總視窗函式，當作 startdate 的引數。

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. 求得 startdate 與 enddate 的差距，並以日期部分字串表示

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

以下為結果集。

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

範例：Azure Synapse Analytics 和 Analytics Platform System (PDW)