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 日期和时间数据类型及函数的概述,请参阅日期和时间数据类型及函数 (Transact-SQL)。
语法
DATEDIFF ( datepart , startdate , enddate )
参数
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 |
weekday | dw, w |
hour | hh |
minute | mi, n |
second | ss, s |
millisecond | ms |
microsecond | mcs |
nanosecond | ns |
注意
每个特定的 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'。
若 bigint 的返回值超出范围(-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 值 。 这些日期是相邻的,它们在时间上相差一百纳秒(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
将对 datepart week 返回 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
可能会溢出,且其精度为分钟或更高 。
示例
以下示例使用不同类型的表达式作为 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 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 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 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 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)
以下示例使用不同类型的表达式作为 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;
-- Returns: 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