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

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

此函数返回指定的 startdate 和 enddate 之间所跨的指定 datepart 边界的计数(作为带符号整数值) 。This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

有关处理 startdate 和 enddate 值之间较大差异的函数,请参阅 DATEDIFF_BIG (TRANSACT-SQ)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 )  

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法,请参阅早期版本文档To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

datepartdatepart
DATEDIFF 用于报告 startdate 与 enddate 之间差异的单位。The units in which DATEDIFF reports the difference between the startdate and enddate. 常用 datepart 单位包括 monthsecondCommonly used datepart units include month or second.

datepart 值不能在变量中指定,也不能指定为带引号的字符串,如 'month'The datepart value cannot be specified in a variable, nor as a quoted string like 'month'.

下表列出了所有有效的 datepart 值 。The following table lists all the valid datepart values. DATEDIFF 接受 datepart 的全名或任何列出的全名缩写形式。DATEDIFF accepts either the full name of the datepart, or any listed abbreviation of the full name.

datepart 名称 datepart name datepart 缩写 datepart abbreviation
yearyear yy, yyyyyy, yyyy
quarter quarter qq, qqq, q
month month mm, mmm, m
dayofyear dayofyear dy, ydy, y
day day dd, ddd, d
week week wk, wwwk, ww
hour hour hhhh
minute minute mi, nmi, n
second second ss, sss, s
millisecond millisecond ms ms
microsecond microsecond mcs mcs
nanosecond nanosecond ns ns
   

备注

每个特定的 datepart 名称及其相应名称的缩写将返回相同的值 。Each specific datepart name and abbreviations for that datepart name will return the same value.

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.

enddate enddate
请参阅 startdate 。See startdate.

返回类型Return Type

intint

返回值Return Value

startdate 与 enddate 之间的 int 差异,以 datepart 设置的边界表示 。The int difference between the startdate and enddate, expressed in the boundary set by datepart.

例如,SELECT DATEDIFF(day, '2036-03-01', '2036-02-28'); 返回 -2,提示 2036 必须为闰年。For example, SELECT DATEDIFF(day, '2036-03-01', '2036-02-28'); returns -2, hinting that 2036 must be a leap year. 这种情况意味着如果从 startdate '2036-03-01' 开始,然后计数 -2 天,则会得到 enddate '2036-02-28'。This case means that if we start at startdate '2036-03-01', and then count -2 days, we reach the enddate of '2036-02-28'.

若 bigint 的返回值超出范围(-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. 对于 millisecond,startdate 和 enddate 之间的最大差值为 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.

如果为 startdate 和 enddate 都只指定了时间值,并且 datepart 不是时间 datepart,则 DATEDIFF 返回 0 。If startdate and enddate are both assigned only a time value, and the datepart is not a time datepart, DATEDIFF returns 0.

DATEDIFF 使用 startdate 或 enddate 的时区偏移部分来计算返回值。DATEDIFF uses the time zone offset component of startdate or enddate to calculate the return value.

由于 smalldatetime 仅精确到分钟,因此在 startdate 或 enddate 具有 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. 如果 startdate 和 enddate 中有一个只含时间部分,另一个只含日期部分,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.

如果 startdate 和 enddate 具有不同的日期数据类型,并且其中一个的时间部分或秒小数部分精度比另一个高,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. 这些日期是相邻的,它们在时间上相差一百纳秒(0.0000001 秒)。Those dates are adjacent and they differ in time by a hundred nanoseconds (.0000001 second). 每个语句中 startdate 与 enddate 之间的差跨其 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.

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 将对 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.

备注Remarks

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. 必须先将字符串显式转换为 datetime 或 smalldatetime 类型,然后才能使用 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.

如果 enddate 和 startdate 之间的差值返回一个超出 int 范围的值,DATEDIFF 可能会溢出,且其精度为分钟或更高 。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

以下示例使用不同类型的表达式作为 startdate 和 enddate 形参的实参 。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

在此示例中,用户定义的变量充当 startdate 和 enddate 的参数 。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

此示例使用标量系统函数作为 startdate 和 enddate 的参数 。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

此示例使用标量子查询和标量函数作为 startdate 和 enddate 的参数 。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

此示例使用字符常量作为 startdate 和 enddate 的参数 。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

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

下面是结果集:Here is the result set.

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

示例:Azure Synapse AnalyticsAzure Synapse Analytics并行数据仓库Parallel Data WarehouseExamples: Azure Synapse AnalyticsAzure Synapse Analytics and 并行数据仓库Parallel Data Warehouse

以下示例使用不同类型的表达式作为 startdate 和 enddate 形参的实参 。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

此示例使用标量子查询和标量函数作为 startdate 和 enddate 的参数 。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

此示例使用字符常量作为 startdate 和 enddate 的参数 。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)