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 名稱和該 datepart 名稱的縮寫都會傳回相同值。
startdate
可解析成下列其中一個值的運算式：
- date
- datetime
- datetimeoffset
- datetime2
- smalldatetime
- time
針對 date，DATEDIFF_BIG 會接受資料行運算式、運算式、字串常值或使用者定義變數。 字串常值必須解析成 datetime。 請使用四位數年份以避免模糊不清的問題。 DATEDIFF_BIG 會從 enddate 減去 startdate。 若要避免模糊不清，請使用四位數年份。 如需兩位數年份的資訊，請參閱設定兩位數年份的截止伺服器設定選項。
enddate
請參閱＜startdate＞。
傳回類型
帶正負號的 bigint
傳回值
傳回 startdate 和 enddate 之間的 bigint 差異，以 datepart 設定的界限表示。
針對超出 bigint 範圍 (-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807) 的傳回值，DATEDIFF_BIG 會傳回錯誤。 不會傳回 int 以致造成 minute 或更高的溢位，DATEDIFF_BIG 只會在使用 nanosecond 精確度 (enddate 和 startdate 的差距超過 292 年另 3 個月 10 天 23 小時 47 分鐘又 16.8547758 秒) 時溢位。
如果 startdate 和 enddate 都只獲派時間值，但 datepart 不是時間 datepart，則 DATEDIFF_BIG 會傳回 0。
DATEDIFF_BIG 確實會使用 startdate 或 enddate 的時區時差元件計算傳回值。
針對用於 startdate 或 enddate 的 smalldatetime 值，DATEDIFF_BIG 一律會在傳回值中將秒和毫秒設定為 0，因為 smalldatetime 的精確度只有到分鐘。
如果您只有將時間值指派給日期資料類型變數，DATEDIFF_BIG 會將遺漏日期部分的值設定為預設值：1900-01-01。 如果您只有將日期值指派給時間或日期資料類型的變數，DATEDIFF_BIG 會將遺漏時間部分的值設定為預設值：00:00:00。 如果 startdate 或 enddate 其中之一只有時間部分，而另一個只有日期部分，DATEDIFF_BIG 會將遺漏的時間和日期部分設定為預設值。
如果 startdate 和 enddate 具有不同的日期資料類型，而且其中一個項目的時間部分或小數秒數有效位數超過另一個項目，DATEDIFF_BIG 會將另一個項目的遺漏部分設定為 0。
datepart 界限
下列陳述式具有相同的 startdate 和相同的 enddate 值。 這些日期都很接近且時間差距為一百奈秒 (.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 可使用 nanosecond 的精確度進行溢位。
範例
指定 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) 中更緊密相關的範例。