DATEDIFF (Transact-SQL)DATEDIFF (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

此函式會傳回跨越指定 startdateenddate 之指定 datepart 界限的計數 (作為帶正負號的整數值)。This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

如需處理 startdateenddate 值之間較大差異的函式,請參閱 DATEDIFF_BIG (Transact-SQL)See DATEDIFF_BIG (Transact-SQL) for a function that handles larger differences between the startdate and enddate values. 如需所有 Transact-SQLTransact-SQL 日期和時間資料類型與函式的概觀,請參閱日期和時間資料類型與函式 (Transact-SQL)See Date and Time Data Types and Functions (Transact-SQL) for an overview of all Transact-SQLTransact-SQL date and time data types and functions.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

DATEDIFF ( datepart , startdate , enddate )  

引數Arguments

datepartdatepart
指定所跨越界限類型之 startdateenddate 的一部分。The part of startdate and enddate that specifies the type of boundary crossed. DATEDIFF 不會接受使用者定義變數對等項目。DATEDIFF will not accept user-defined variable equivalents. 此表格會列出所有有效的 datepart 引數。This table lists all valid datepart arguments.

datepartdatepart 縮寫Abbreviations
yearyear yy, yyyyyy, yyyy
quarterquarter qq, qqq, q
monthmonth mm, mmm, m
dayofyeardayofyear dy, ydy, y
dayday dd, ddd, d
weekweek wk, wwwk, ww
hourhour hhhh
minuteminute mi, nmi, n
secondsecond ss, sss, s
millisecondmillisecond msms
microsecondmicrosecond mcsmcs
nanosecondnanosecond nsns

startdatestartdate
可解析成下列其中一個值的運算式:An expression that can resolve to one of the following values:

  • datedate
  • datetimedatetime
  • datetimeoffsetdatetimeoffset
  • datetime2datetime2
  • smalldatetimesmalldatetime
  • timetime

請使用四位數年份以避免模糊不清。Use four-digit years to avoid ambiguity. 如需兩位數年份值的資訊,請參閱設定兩位數年份的截止伺服器設定選項See Configure the two digit year cutoff Server Configuration Option for information about two-digit year values.

enddateenddate
請參閱<startdate>。See startdate.

傳回類型Return Type

intint

傳回值Return Value

每個特定 datepart 和該 datepart 的縮寫會傳回相同的值。Each specific datepart and the abbreviations for that datepart will return the same value.

針對超出 int 範圍 (-2,147,483,648 到 +2,147,483,647) 的傳回值,DATEDIFF 會傳回錯誤。For a return value out of range for int (-2,147,483,648 to +2,147,483,647), DATEDIFF returns an error. 針對 millisecondstartdateenddate 最大的差異為 24 天 20 小時 31 分鐘 23.647 秒。For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. 針對 second,最大的差異為 68 年 19 天 3 小時 14 分鐘 7 秒。For second, the maximum difference is 68 years, 19 days, 3 hours, 14 minutes and 7 seconds.

如果 startdateenddate 都只獲指派時間值,且 datepart 不是時間 datepartDATEDIFF 會傳回 0。If startdate and enddate are both assigned only a time value, and the datepart is not a time datepart, DATEDIFF returns 0.

DATEDIFF 不會使用 startdateenddate 的時區時差元件來計算傳回值。DATEDIFF does not use a time zone offset component of startdate or enddate to calculate the return value.

由於 smalldatetime 的精確度只有到分鐘,因此當 startdateenddate 具有 smalldatetime 值時,秒和毫秒就一律會在傳回值中設定為 0。Because smalldatetime is accurate only to the minute, seconds and milliseconds are always set to 0 in the return value when startdate or enddate have a smalldatetime value.

如果您只有將時間值指派給日期資料類型變數,DATEDIFF 會將遺漏日期部分的值設定為預設值:1900-01-01If only a time value is assigned to a date data type variable, DATEDIFF sets the value of the missing date part to the default value: 1900-01-01. 如果您只有將日期值指派給時間或日期資料類型的變數,DATEDIFF 會將遺漏時間部分的值設定為預設值:00:00:00If only a date value is assigned to a variable of a time or date data type, DATEDIFF sets the value of the missing time part to the default value: 00:00:00. 如果 startdateenddate 其中之一只有時間部分,而另一個只有日期部分,DATEDIFF 會將遺漏的時間和日期部分設定為預設值。If either startdate or enddate have only a time part and the other only a date part, DATEDIFF sets the missing time and date parts to the default values.

如果 startdateenddate 具有不同的日期資料類型,而且其中一個項目的時間部分或小數秒數有效位數超過另一個項目,DATEDIFF 會將另一個項目的遺漏部分設定為 0。If startdate and enddate have different date data types, and one has more time parts or fractional seconds precision than the other, DATEDIFF sets the missing parts of the other to 0.

datepart 界限datepart boundaries

下列陳述式具有相同的 startdate 和相同的 enddate 值。The following statements have the same startdate and the same enddate values. 這些日期都很接近且時間差距為一百奈秒 (.0000001 秒)。Those dates are adjacent and they differ in time by a hundred nanoseconds (.0000001 second). 每個陳述式中 startdateenddate 之間的差異會跨越其 datepart 的日曆或時間界限。The difference between the startdate and enddate in each statement crosses one calendar or time boundary of its datepart. 每個陳述式都會傳回 1。Each statement returns 1. 如果 startdateenddate 具有不同的年份值,但具有相同的日曆週值,DATEDIFF 會針對 datepart week 傳回 0。If startdate and enddate have different year values but they have the same calendar week values, DATEDIFF will return 0 for datepart week.

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

RemarksRemarks

您可以在 SELECT <list>WHEREHAVINGGROUP BYORDER BY 子句中使用 DATEDIFFUse DATEDIFF in the SELECT <list>, WHERE, HAVING, GROUP BY and ORDER BY clauses.

DATEDIFF 會以隱含的方式,將字串常值轉換為 datetime2 類型。DATEDIFF implicitly casts string literals as a datetime2 type. 這表示,將日期當作字串傳遞時,DATEDIFF 不支援 YDM 格式。This means that DATEDIFF does not support the format YDM when the date is passed as a string. 您必須明確地將字串轉換為 datetimesmalldatetime 類型,才能使用 YDM 格式。You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

指定 SET DATEFIRSTDATEDIFF 沒有任何作用。Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF 一律會使用星期天當作一週的第一天,以確保此函式以具決定性的方式運作。DATEDIFF always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.

如果 enddatestartdate 的差距傳回超出 int 範圍的值,則 DATEDIFF 可使用 minute 或更高的精確度進行溢位。DATEDIFF may overflow with a precision of minute or higher if the difference between enddate and startdate returns a value that is out of range for int.

範例Examples

這些範例會使用不同的運算式類型,當作 startdateenddate 參數的引數。These examples use different types of expressions as arguments for the startdate and enddate parameters.

A.A. 指定 startdate 和 enddate 的資料行Specifying columns for startdate and enddate

此範例會計算資料表的兩個資料行日期之間跨越界限的天數。This example calculates the number of day boundaries crossed between dates in two columns in a table.

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.B. 指定 startdate 和 enddate 的使用者自訂變數Specifying user-defined variables for startdate and enddate

在此範例中,會以使用者定義的變數作為 startdateenddate 的引數。In this example, user-defined variables serve as arguments for startdate and 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.C. 指定 startdate 和 enddate 的純量系統函數Specifying scalar system functions for startdate and enddate

此範例會使用純量系統函數,當作 startdateenddate 的引數。This example uses scalar system functions as arguments for startdate and enddate.

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

D.D. 指定 startdate 和 enddate 的純量子查詢和純量函數Specifying scalar subqueries and scalar functions for startdate and enddate

此範例會使用純量子查詢和純量函數,當作 startdateenddate 的引數。This example uses scalar subqueries and scalar functions as arguments for startdate and enddate.

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

E.E. 指定 startdate 和 enddate 的常數Specifying constants for startdate and enddate

此範例會使用字元常數,當作 startdateenddate 的引數。This example uses character constants as arguments for startdate and enddate.

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

F.F. 指定 enddate 的數值運算式和純量系統函數Specifying numeric expressions and scalar system functions for enddate

此範例會使用數值運算式 (GETDATE() + 1) 和純量系統函數 GETDATESYSDATETIME,當作 enddate 的引數。This example uses a numeric expression, (GETDATE() + 1), and scalar system functions GETDATE and SYSDATETIME, as arguments for 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.G. 指定 startdate 的排名函數Specifying ranking functions for startdate

此範例會使用次序函數,當作 startdate 的引數。This example uses a ranking function as an argument for 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.H. 指定 startdate 的彙總視窗函數Specifying an aggregate window function for startdate

此範例會使用彙總視窗函式,當作 startdate 的引數。This example uses an aggregate window function as an argument for 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.I. 求得 startdate 與 enddate 的差距,並以日期部分字串表示Finding difference between startdate and enddate as date parts strings

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

以下為結果集:Here is the result set.

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

範例:Azure SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseExamples: Azure SQL 資料倉儲Azure SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse

這些範例會使用不同的運算式類型,當作 startdateenddate 參數的引數。These examples use different types of expressions as arguments for the startdate and enddate parameters.

J.J. 指定 startdate 和 enddate 的資料行Specifying columns for startdate and enddate

此範例會計算資料表的兩個資料行日期之間跨越界限的天數。This example calculates the number of day boundaries crossed between dates in two columns in a table.

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;  
-- Returns: 1  

K.K. 指定 startdate 和 enddate 的純量子查詢和純量函數Specifying scalar subqueries and scalar functions for startdate and enddate

此範例會使用純量子查詢和純量函數,當作 startdateenddate 的引數。This example uses scalar subqueries and scalar functions as arguments for startdate and enddate.

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

L.L. 指定 startdate 和 enddate 的常數Specifying constants for startdate and enddate

此範例會使用字元常數,當作 startdateenddate 的引數。This example uses character constants as arguments for startdate and 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.M. 指定 startdate 的排名函數Specifying ranking functions for startdate

此範例會使用次序函數,當作 startdate 的引數。This example uses a ranking function as an argument for startdate.

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

N.N. 指定 startdate 的彙總視窗函數Specifying an aggregate window function for startdate

此範例會使用彙總視窗函式,當作 startdate 的引數。This example uses an aggregate window function as an argument for startdate.

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

另請參閱See also

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