DATEDIFF (Transact-SQL)

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

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

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

Transact-SQL 語法慣例

語法

DATEDIFF ( datepart , startdate , enddate )

引數

datepart

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

無法在變數中指定 datepart 值，也不能指定為引號字串，例如 'month'。

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

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

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

startdate

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

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

請使用四位數年份以避免模糊不清。 如需兩位數年份值的相關信息，請參閱 伺服器設定：兩位數年份截止 。

enddate

請參閱＜startdate＞。

傳回類型

int

傳回值

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

例如，傳回 -2，SELECT DATEDIFF(day, '2036-03-01', '2036-02-28');提示 2036 必須是閏年。 此案例表示如果我們從 startdate 2036-03-01開始，然後計算-2天數，就會到達 的 2036-02-28enddate。

針對超出 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 不是 time 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(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');

如果 startdate 和 enddate 有不同的年份值，但它們具有相同的行事歷周值，DATEDIFF則會0傳回 datepart week。

備註

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

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

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

DATEDIFF如果 enddate 與 startdate 之間的差異傳回 int 範圍超出範圍的值，則可能會溢minute位。

範例

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects （Microsoft SQL Server 範例和社群專案）首頁即可下載。

這些範例會使用不同的運算式類型，當作 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;

結果集如下所示。

Duration
--------
1

B. 指定 startdate 和 enddate 的使用者定義變數

在此範例中，會以使用者定義的變數作為 startdate 和 enddate 的引數。

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. 指定 startdate 和 enddate 的純量系統函式

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

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

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

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

USE AdventureWorks2022;
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 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. 指定 startdate 的排名函式

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

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

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

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. 以日期元件字串的形式尋找 startdate 和 enddate 之間的差異

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

結果集如下所示。

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

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

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

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

Duration
--------
1

K. 指定 startdate 和 enddate 的純量子查詢和純量函式

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

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

L. 指定 startdate 和 enddate 的常數

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

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

M. 指定 startdate 的排名函式

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

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

N. 指定 startdate 的匯總視窗函式

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

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

相關內容

• DATEDIFF_BIG (Transact-SQL)
• CAST 和 CONVERT (Transact-SQL)