DATEDIFF (Transact-SQL)

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

この関数は、startdate と enddate で指定された 2 つの日付間の差を、指定された datepart 境界の数で (符号付き整数値として) で返します。

開始日 と enddate の値の大きな違いを処理する関数については、DATEDIFF_BIGを参照してください。 Transact-SQL のすべての日付と時刻のデータ型と関数の概要については日付と時刻のデータ型と関数に関するページを参照してください。

Transact-SQL 構文表記規則

構文

DATEDIFF ( datepart , startdate , enddate )

引数

datepart

DATEDIFFが開始日と終了日の差を報告する単位を指定します。 一般的に使用される datepart の単位には、month または second が含まれます。

datepart値は変数に指定することも、'month'のような引用符で囲まれた文字列として指定することもできません。

次の表に、すべての有効な datepart 値の一覧を示します。 DATEDIFF は、 datepart の完全名、または完全名の省略形を受け取ります。

特定の datepart 名前とその datepart 名の省略形は、同じ値を返します。

startdate

次のいずれかの値に解決できる式。

• date
• datetime
• datetimeoffset
• datetime2
• smalldatetime
• time

4 桁の西暦を使用して、あいまいさを排除します。 2 桁の年の値については、「 Server の構成: 2 桁の年のカットオフ 」を参照してください。

enddate

「startdate」をご覧ください。

戻り値の型

int

戻り値

datepart により設定された境界に表示された、startdate と enddate の間の int 差。

たとえば、 SELECT DATEDIFF(day, '2036-03-01', '2036-02-28'); は -2を返し、2036 年を閏年にする必要があることを示します。 この場合、開始日 2036-03-01開始してから-2日を数える場合、2036-02-28の終了日に達します。

int の範囲 (-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 の値が指定されています。 これらの日付は隣接しており、時間的な差は 100 ナノ秒 (0.0000001 秒) です。 各ステートメントにおける startdate と enddate の差は、どの要素をとっても、datepart の 1 単位分となるように配慮されています。 各ステートメントは 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');

開始日と終了日年の値は異なりますが、カレンダーの週の値が同じである場合、DATEDIFFはdatepart weekの0を返します。

解説

SELECT <list>、WHERE、HAVING、GROUP BY、およびORDER BY句でDATEDIFFを使用します。

DATEDIFF は、文字列リテラルを datetime2 型として暗黙的にキャストします。 つまり、 DATEDIFF では、日付が文字列として渡されるときに YDM 形式はサポートされません。 YDM形式を使用するには、文字列を明示的に datetime または smalldatetime 型にキャストする必要があります。

SET DATEFIRST を指定しても、DATEDIFF には何の影響もありません。 DATEDIFF では、週の最初の曜日として常に日曜日を使用し、関数が決定的な方法で動作するようにします。

DATEDIFFenddate と startdate の差がintの範囲外の値を返す場合は、minute以上の精度でオーバーフローする可能性があります。

例

この記事の Transact-SQL コード サンプルは AdventureWorks2022 または AdventureWorksDW2022 サンプル データベースを使用します。このサンプル データベースは、Microsoft SQL Server サンプルとコミュニティ プロジェクトのホーム ページからダウンロードできます。

次の例では、startdate パラメーターと enddate パラメーターの引数として、各種の式を使用しています。

A. 開始日と終了日の列を指定する

この例では、テーブルの 2 つの列に日付を格納し、両者の差を日単位で計算しています。

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;

結果セットは次のとおりです。

Duration
--------
1

B. 開始日と終了日にユーザー定義変数を指定する

この例では、ユーザー定義変数が startdate と enddate の引数として機能します。

DECLARE @startdate AS DATETIME2 = '2007-05-05 12:10:09.3312722';
DECLARE @enddate AS DATETIME2 = '2007-05-04 12:10:09.3312722';

SELECT DATEDIFF(day, @startdate, @enddate);

C: 開始日と終了日にスカラー システム関数を指定する

この例では、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 の引数として文字定数を使用しています。

SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', '2007-05-08 09:53:01.0376635');

F. enddate に数値式とスカラー システム関数を指定する

この例では、enddate の引数として、数値式 (GETDATE() + 1) のほか、スカラー システム関数 GETDATE および SYSDATETIME を使用しています。

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 の引数として順位付け関数を使用しています。

USE AdventureWorks2022;
GO

SELECT p.FirstName,
       p.LastName,
       DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY a.PostalCode), SYSDATETIME()) AS 'Row Number'
FROM Sales.SalesPerson AS s
     INNER JOIN Person.Person AS p
         ON s.BusinessEntityID = p.BusinessEntityID
     INNER JOIN Person.Address AS a
         ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
      AND SalesYTD <> 0;

H. 開始日の集計ウィンドウ関数を指定する

この例では、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 AS sod
     INNER JOIN Sales.SalesOrderHeader AS soh
         ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.SalesOrderID IN (43659, 58918);
GO

I. 開始日と終了日の違いを日付パーツ文字列として検索する

-- DOES NOT ACCOUNT FOR LEAP YEARS
DECLARE @date1 AS DATETIME, @date2 AS DATETIME, @result AS VARCHAR (100);

DECLARE @years AS INT, @months AS INT, @days AS INT, @hours AS INT, @minutes AS INT, @seconds AS INT, @milliseconds AS 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. 開始日と終了日の列を指定する

この例では、テーブルの 2 つの列に日付を格納し、両者の差を日単位で計算しています。

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;

Duration
--------
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 の引数として文字定数を使用しています。

-- 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 の引数として順位付け関数を使用しています。

-- Uses AdventureWorks
SELECT FirstName,
       LastName,
       DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY DepartmentName), SYSDATETIME()) AS RowNumber
FROM dbo.DimEmployee;

北 開始日の集計ウィンドウ関数を指定する

この例では、startdate の引数として集計関数を使用しています。

-- Uses AdventureWorks
SELECT FirstName,
       LastName,
       DepartmentName,
       DATEDIFF(year, MAX(HireDate) OVER (PARTITION BY DepartmentName), SYSDATETIME()) AS SomeValue
FROM dbo.DimEmployee;

関連するコンテンツ

• DATEDIFF_BIG (Transact-SQL)
• CAST および CONVERT (Transact-SQL)