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。

日期部分值不能在变量中指定，也不能指定为带引号的字符串'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

该日期部分名称的每个特定日期部分名称和缩写都返回相同的值。

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 必须是跃年。 本例意味着，如果我们从开始日期开始，然后计算-2天数，我们将达到结束日期2036-02-28。2036-03-01

若 bigint 的返回值超出范围（-2,147,483,648 到 +2,147,483,647），DATEDIFF 返回错误 。 对于millisecond，开始日期和结束日期之间的最大差值为 24 天、20 小时、31 分钟和 23.647 秒。 对于 second，最大差值为 68 年、19 天、3 小时、14 分钟和 7 秒。

如果 startdate 和 enddate 都只分配一个时间值，并且 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 值 。 这些日期是相邻的，它们在时间上相差一百纳秒（0.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返回 datepartweek。

注解

在 DATEDIFF 、WHERE、HAVING和GROUP BYORDER 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 示例和社区项目主页下载它。

以下示例使用不同类型的表达式作为 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)