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

適用対象:Applies to: はいSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 以降 はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database はいAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance適用対象:Applies to: はいSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database はいAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance

この関数は、startdateenddate で指定された 2 つの日付間の差を、指定された 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 では、ユーザー定義変数からの、または引用符で囲まれた文字列としての datepart 値は受け入れられません。DATEDIFF_BIG will not accept datepart values from user-defined variables or as quoted strings.

この表には、有効な datepart 引数名と省略形をすべて一覧表示しています。This table lists all valid datepart argument names and abbreviations.

datepartdatepart name datepart 省略形datepart abbreviation
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

注意

特定の各 datepart 名と、その datepart 名の省略形では、同じ値が返されます。Each specific datepart name and abbreviations for that datepart name will return the same value.

startdatestartdate
次のいずれかの値に解決できる式。An expression that can resolve to one of the following values:

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

date の場合、DATEDIFF_BIG では、列式、式、文字列リテラル、ユーザー定義の変数が受け入れられます。For date, DATEDIFF_BIG will accept a column expression, expression, string literal, or user-defined variable. 文字列リテラル値は datetime に解決する必要があります。A string literal value must resolve to a datetime. あいまいさの問題を排除するために、4 桁の西暦を使用してください。Use four-digit years to avoid ambiguity issues. DATEDIFF_BIG では、enddate から startdate が減算されます。DATEDIFF_BIG subtracts startdate from enddate. こうしたあいまいさを排除するため、4 桁の西暦を使用してください。To avoid ambiguity, use four-digit years. 2 桁の西暦については、「two digit year cutoff サーバー構成オプションの構成」を参照してください。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

datepart により設定された境界に表示された、startdateenddate の間の bigint 差を返します。Returns the bigint difference between the startdate and enddate, expressed in the coundary set by datepart.

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. int を返すため minute 以上の精度でオーバーフローする可能性がある DATEDIFF とは異なり、DATEDIFF_BIGnanosecond の精度を使用する場合にのみオーバーフローする可能性があります。この場合、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 が時刻の datepart でない場合、DATEDIFF_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 では、戻り値を計算する際に、startdate または enddate のタイム ゾーン オフセット要素を使用しません。DATEDIFF_BIG does not use a time zone offset component of startdate or enddate to calculate the return value.

smalldatetime の精度は分単位までなので、startdate または enddatesmalldatetime 値を使用した場合、DATEDIFF_BIG では、戻り値で秒とミリ秒が常に 0 に設定されます。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-01 に設定されます。If 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:00 に設定されます。If 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. startdate または enddate のいずれか一方が時刻要素のみで、もう一方が日付要素のみであった場合、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

次の各ステートメントには、すべて同じ startdateenddate の値が指定されています。The following statements have the same startdate and the same enddate values. これらの日付は隣接しており、時間的な差は 1 マイクロ秒 (0.0000001 秒) です。Those dates are adjacent and they differ in time by one microsecond (.0000001 second). 各ステートメントにおける startdateenddate の差は、どの要素をとっても、datepart の 1 単位分となるように配慮されています。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');

解説Remarks

DATEDIFF_BIG は、SELECT <list>WHEREHAVINGGROUP BYORDER BY 句で使用します。Use 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. 文字列を明示的にキャストする必要があります、 datetime または smalldatetime YDM 形式を使用する型。You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

SET DATEFIRST を指定しても、DATEDIFF_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_BIGnanosecond の精度でオーバーフローする可能性があります。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

この例では、startdate パラメーターと enddate パラメーターの引数として、各種の式を使用しています。This example uses different types of expressions as arguments for the startdate and enddate parameters. テーブルの 2 つの列に日付を格納し、両者の差を日単位で計算しています。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)