DATEDIFF_BIG (Transact-SQL)
适用于：
SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库 Azure SQL 托管实例
此函数返回指定的 startdate 和 enddate 之间所跨的指定 datepart 边界的计数（作为带符号大整数值）。
有关所有 Transact-SQL 日期和时间数据类型及函数的概述，请参阅日期和时间数据类型及函数 (Transact-SQL)。
语法
DATEDIFF_BIG ( datepart , startdate , enddate )
参数
datepart
指定所跨边界类型的 startdate 和 enddate 的一部分。
注意
DATEDIFF_BIG 不会接受来自用户定义的变量或作为带引号的字符串的 datepart 值。
此表列出了所有有效的 datepart 参数名称和缩写。
| datepart 名称 | datepart 缩写 |
|---|---|
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| day | dd, d |
| week | wk, ww |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
| microsecond | mcs |
| nanosecond | ns |
注意
每个特定的 datepart 名称及其相应名称的缩写将返回相同的值 。
startdate
可解析为下列值之一的表达式：
- date
- datetime
- datetimeoffset
- datetime2
- smalldatetime
- time
对于 date，DATEDIFF_BIG 接受列表达式、表达式、字符串文本或用户定义的变量 。 字符串文字值必须解析为 datetime 。 使用四位数年份可避免含糊不清问题。 DATEDIFF_BIG 从 enddate 中减去 startdate。 为避免不确定性，请使用四位数年份。 有关两位数年份的信息，请参阅配置两位数年份截止服务器配置选项。
enddate
请参阅 startdate 。
返回类型
签名的 bigint
返回值
返回 startdate 与 enddate 之间的 bigint 差异，以 datepart 设置的边界表示 。
若 bigint 的返回值超出范围（-9223372036854775808 到 9223372036854775807），DATEDIFF_BIG 返回错误。 与返回 int 类型且因此可能以分钟或更高精度溢出不同，DATEDIFF_BIG 仅在使用纳秒精度（其中结束日期和开始日期之间的差值大于 292 年 3 个月 10 天 23 小时 47 分钟 16.8547758 秒）时才会溢出。
如果为 startdate 和 enddate 都只指定了时间值，并且 datepart 不是时间 datepart，则 DATEDIFF_BIG 返回 0 。
DATEDIFF_BIG 使用 startdate 或 enddate 的时区偏移量部分来计算返回值 。
对于用于 startdate 或 enddates 的 malldatetime 值，由于 smalldatetime 仅精确到分钟，因此 DATEDIFF_BIG 始终将返回值中的秒和毫秒设置为 0。
如果只为某个日期数据类型变量指定时间值，DATEDIFF_BIG 会将所缺日期部分的值设置为默认值：1900-01-01。 如果只为某个时间或日期数据类型的变量指定日期值，DATEDIFF_BIG 会将所缺时间部分的值设置为默认值：00:00:00。 如果 startdate 和 enddate 中有一个只含时间部分，另一个只含日期部分，DATEDIFF_BIG 会将所缺时间和日期部分设置为各自的默认值 。
如果 startdate 和 enddate 具有不同的日期数据类型，并且其中一个的时间部分或秒小数部分精度比另一个高，DATEDIFF_BIG 会将另一个的所缺部分设置为 0 。
datepart 边界
以下语句具有相同的 startdate 和 enddate 值 。 这些日期是相邻的，它们在时间上相差一百纳秒（0.0000001 秒）。 每个语句中 startdate 与 enddate 之间的差跨其 datepart 的一个日历或时间边界 。 每个语句都返回 1。 如果 startdate 和 enddate 的年份值不同，但它们的日历周值相同，DATEDIFF_BIG 将对 datepart week 返回 0。
SELECT DATEDIFF_BIG(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
注解
在 SELECT <list>、WHERE、HAVING、GROUP BY 和 ORDER BY 子句中使用 DATEDIFF_BIG。
DATEDIFF_BIG 将字符串文字隐式转换为 datetime2 类型 。 这就意味着，日期在作为字符串传递时，DATEDIFF_BIG 不会支持 YDM 格式。 必须先将字符串显式转换为 datetime 或 smalldatetime 类型，然后才能使用 YDM 格式 。
指定 SET DATEFIRST 对 DATEDIFF_BIG 没有影响。 DATEDIFF_BIG 始终使用星期日作为每周的第一天，确保函数以确定性方式运行。
如果 enddate 和 startdate 之间的差值返回一个超出 bigint 范围的值，DATEDIFF_BIG 可能会溢出，且其精度为纳秒 。
示例
为 startdate 和 enddate 指定列
此示例使用不同类型的表达式作为 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_BIG(day, startDate, endDate) AS 'Duration'
FROM dbo.Duration;
-- Returns: 1
查找作为日期部分字符串的 startdate 和 enddate 之间的差异
DECLARE @date1 DATETIME2, @date2 DATETIME2, @result VARCHAR(100)
DECLARE @years BIGINT, @months BIGINT, @days BIGINT, @hours BIGINT, @minutes BIGINT, @seconds BIGINT, @milliseconds BIGINT
SET @date1 = '0001-01-01 00:00:00.00000000'
SET @date2 = '2018-12-12 07:08:01.12345678'
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
结果集如下。
2017 years, 11 months, 11 days, 7 hours, 8 minutes and 1.123 seconds
请参阅 DATEDIFF (Transact-SQL) 中更紧密地相关的示例。