DATEDIFF_BIG (Transact-SQL)DATEDIFF_BIG (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

此函式會傳回跨越指定之 startdateenddate 的指定之 datepart 界限的計數 (作為帶正負號的大整數值)。This function returns the count (as a signed big integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

如需所有 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_BIG ( datepart , startdate , enddate )  

引數Arguments

datepartdatepart
指定所跨越界限類型之 startdateenddate 的一部分。The part of startdate and enddate that specifies the type of boundary crossed. DATEDIFF_BIG 不會接受使用者定義變數對等項目。DATEDIFF_BIG will not accept user-defined variable equivalents. 此表格會列出所有有效的 datepart 引數。This table lists all valid datepart arguments.

注意

DATEDIFF_BIG 不會接受 datepart 引數的使用者定義變數對等項目。DATEDIFF_BIG does not accept user-defined variable equivalents for the datepart arguments.

datepartdatepart 縮寫Abbreviations
yearyear yy, yyyyyy, yyyy
quarterquarter qq, qqq, q
monthmonth mm, mmm, m
dayofyeardayofyear dy, ydy, y
dayday dd, ddd, d
weekweek wk, wwwk, ww
hourhour hhhh
minuteminute mi, nmi, n
secondsecond ss, sss, s
millisecondmillisecond msms
microsecondmicrosecond mcsmcs
nanosecondnanosecond nsns

startdatestartdate
可解析成下列其中一個值的運算式:An expression that can resolve to one of the following values:

  • datedate
  • datetimedatetime
  • datetimeoffsetdatetimeoffset
  • datetime2datetime2
  • smalldatetimesmalldatetime
  • timetime

針對 dateDATEDIFF_BIG 會接受資料行運算式、運算式、字串常值或使用者定義變數。For date, DATEDIFF_BIG will accept a column expression, expression, string literal, or user-defined variable. 字串常值必須解析成 datetimeA string literal value must resolve to a datetime. 請使用四位數年份以避免模糊不清的問題。Use four-digit years to avoid ambiguity issues. DATEDIFF_BIG 會從 enddate 減去 startdateDATEDIFF_BIG subtracts startdate from enddate. 若要避免模糊不清,請使用四位數年份。To avoid ambiguity, use four-digit years. 如需兩位數年份的資訊,請參閱設定兩位數年份的截止伺服器設定選項See Configure the two digit year cutoff Server Configuration Option for information about two-digit years.

enddateenddate
請參閱<startdate>。See startdate.

傳回類型Return Type

帶正負號的 bigintSigned bigint

傳回值Return Value

傳回跨越指定 startdate 和 enddate 之指定 datepart 界限的計數 (作為帶正負號的 Bigint 值)。Returns the count (as a signed bigint value) of the specified datepart boundaries crossed between the specified startdate and enddate.

  • 每個特定 datepart 和該 datepart 的縮寫會傳回相同的值。Each specific datepart and the abbreviations for that datepart will return the same value.

針對超出 bigint 範圍 (-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807) 的傳回值,DATEDIFF_BIG 會傳回錯誤。For a return value out of range for bigint (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807), DATEDIFF_BIG returns an error. 不同於 DATEDIFF 會傳回 int,因此可使用 minute 或更高的精確度進行溢位,DATEDIFF_BIG 只能在使用 nanosecond 精確度 (其中 enddatestartdate 之間的差距超出 292 年 3 個月 10 天 23 小時 47 分鐘又 16.8547758 秒) 時進行溢位。Unlike DATEDIFF which returns an int and therefore may overflow with a precision of minute or higher, DATEDIFF_BIG can only overflow if using nanosecond precision where the difference between enddate and startdate is more than 292 years, 3 months, 10 days, 23 hours, 47 minutes and 16.8547758 seconds.

如果 startdateenddate 都只獲指派時間值,且 datepart 不是時間 datepartDATEDIFF_BIG 會傳回 0。If startdate and enddate are both assigned only a time value, and the datepart is not a time datepart, DATEDIFF_BIG returns 0.

DATEDIFF_BIG 不會使用 startdateenddate 的時區時差元件來計算傳回值。DATEDIFF_BIG does not use a time zone offset component of startdate or enddate to calculate the return value.

針對用於 startdateenddatesmalldatetime 值,DATEDIFF_BIG 一律會在傳回值中將秒和毫秒設定為 0,因為 smalldatetime 的精確度只有到分鐘。For a smalldatetime value used for startdate or enddate, DATEDIFF_BIG always sets seconds and milliseconds to 0 in the return value because smalldatetime only has accuracy to the minute.

如果您只有將時間值指派給日期資料類型變數,DATEDIFF_BIG 會將遺漏日期部分的值設定為預設值:1900-01-01If only a time value is assigned to a date data type variable, DATEDIFF_BIG sets the value of the missing date part to the default value: 1900-01-01. 如果您只有將日期值指派給時間或日期資料類型的變數,DATEDIFF_BIG 會將遺漏時間部分的值設定為預設值:00:00:00If only a date value is assigned to a variable of a time or date data type, DATEDIFF_BIG sets the value of the missing time part to the default value: 00:00:00. 如果 startdateenddate 其中之一只有時間部分,而另一個只有日期部分,DATEDIFF_BIG 會將遺漏的時間和日期部分設定為預設值。If either startdate or enddate have only a time part and the other only a date part, DATEDIFF_BIG sets the missing time and date parts to the default values.

如果 startdateenddate 具有不同的日期資料類型,而且其中一個項目的時間部分或小數秒數有效位數超過另一個項目,DATEDIFF_BIG 會將另一個項目的遺漏部分設定為 0。If startdate and enddate have different date data types, and one has more time parts or fractional seconds precision than the other, DATEDIFF_BIG 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. 這些日期都很接近且時間差距為一微秒 (.0000001 秒)。Those dates are adjacent and they differ in time by one microsecond (.0000001 second). 每個陳述式中 startdateenddate 之間的差異會跨越其 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. 如果 startdateenddate 具有不同的年份值,但具有相同的日曆週值,DATEDIFF_BIG 會針對 datepart week 傳回 0。If startdate and enddate have different year values but they have the same calendar week values, DATEDIFF_BIG will return 0 for datepart week.

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');

RemarksRemarks

您可以在 SELECT <list>WHEREHAVINGGROUP BYORDER BY 子句中使用 DATEDIFF_BIGUse DATEDIFF_BIG in the SELECT <list>, WHERE, HAVING, GROUP BY and ORDER BY clauses.

DATEDIFF_BIG 會以隱含的方式,將字串常值轉換為 datetime2 類型。DATEDIFF_BIG implicitly casts string literals as a datetime2 type. 這表示,將日期當作字串傳遞時,DATEDIFF_BIG 不支援 YDM 格式。This means that DATEDIFF_BIG does not support the format YDM when the date is passed as a string. 您必須明確地將字串轉換為 datetimesmalldatetime 類型,才能使用 YDM 格式。You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

指定 SET DATEFIRSTDATEDIFF_BIG 沒有任何作用。Specifying SET DATEFIRST has no effect on DATEDIFF_BIG. DATEDIFF_BIG 一律會使用星期天當作一週的第一天,以確保此函式以具決定性的方式運作。DATEDIFF_BIG always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.

如果 enddatestartdate 的差距傳回超出 bigint 範圍的值,則 DATEDIFF_BIG 可使用 nanosecond 的精確度進行溢位。DATEDIFF_BIG may overflow with a precision of nanosecond if the difference between enddate and startdate returns a value that is out of range for bigint.

範例Examples

指定 startdate 和 enddate 的資料行Specifying columns for startdate and enddate

此範例會使用不同的運算式類型,當作 startdateenddate 參數的引數。This example uses different types of expressions as arguments for the startdate and enddate parameters. 它會計算資料表的兩個資料行之間的日期已跨越的天數界限。It calculates the number of day boundaries crossed between dates in two columns of 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_BIG(day, startDate, endDate) AS 'Duration'  
    FROM dbo.Duration;  
-- Returns: 1  

求得 startdate 與 enddate 的差距,並以日期部分字串表示Finding difference between startdate and enddate as date parts strings

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

以下為結果集:Here is the result set.

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

請參閱 DATEDIFF (Transact-SQL) 中更緊密相關的範例。See more closely related examples in DATEDIFF (Transact-SQL).

另請參閱See also

CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)
DATEDIFF (Transact-SQL)DATEDIFF (Transact-SQL)