DATETRUNC (Transact-SQL)

適用対象: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance Microsoft Fabric のSQL 分析エンドポイント Microsoft Fabric のウェアハウス

DATETRUNC 関数は、入力の date を指定した datepart に切り詰めて返します。

構文

DATETRUNC ( datepart , date )

引数

datepart

切り詰めの精度を指定します。 次の表は、DATETRUNC に対して有効なすべての datepart の値の一覧です。入力の日時型でも有効な部分であるとします。

date

T-SQL で有効ないずれかの日付または時刻型に解決できる任意の式、列、またはユーザー定義変数を受け付けます。 有効な型は次のとおりです。

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

date パラメーターと date データ型を混同しないでください。

DATETRUNC は、 datetime2(7)に解決できる文字列リテラル (任意の文字列型) も受け入れます。

戻り値の型

DATETRUNC の返されるデータ型は動的です。 DATETRUNC は、入力の日時と同じデータ型 (および該当する場合は、同じ小数部の時間スケール) の切り詰められた日時を返します。 たとえば、DATETRUNC の入力日時が datetimeoffset(3) の場合、datetimeoffset(3) が返されます。 datetime2(7) に解決できる文字列リテラルが指定された場合、DATETRUNC は datetime2(7) を返します。

小数部の時間スケールの精度

ミリ秒の小数部の時間スケールは 3 (.123)、マイクロ秒の小数部の時間スケールは 6 (.123456)、ナノ秒の小数部の時間スケールは 9 (.123456789) です。 time、datetime2、datetimeoffset データ型では、最大の小数部時間スケールは 7 (.1234567) です。 したがって、 millisecond datepart に切り捨てるには、小数部の時間スケールを少なくとも 3 にする必要があります。 同様に、 microsecond datepart に切り捨てるには、小数部の時間スケールを 6 以上にする必要があります。 DATETRUNCT-SQL の日付型では 9 の小数部の時間スケールがサポートされていないためnanosecond datepart はサポートされません。

例

A. さまざまな datepart オプションを使う

次の例は、さまざまな datepart オプションの使用法を示しています。

DECLARE @d datetime2 = '2021-12-08 11:30:15.1234567';
SELECT 'Year', DATETRUNC(year, @d);
SELECT 'Quarter', DATETRUNC(quarter, @d);
SELECT 'Month', DATETRUNC(month, @d);
SELECT 'Week', DATETRUNC(week, @d); -- Using the default DATEFIRST setting value of 7 (U.S. English)
SELECT 'Iso_week', DATETRUNC(iso_week, @d);
SELECT 'DayOfYear', DATETRUNC(dayofyear, @d);
SELECT 'Day', DATETRUNC(day, @d);
SELECT 'Hour', DATETRUNC(hour, @d);
SELECT 'Minute', DATETRUNC(minute, @d);
SELECT 'Second', DATETRUNC(second, @d);
SELECT 'Millisecond', DATETRUNC(millisecond, @d);
SELECT 'Microsecond', DATETRUNC(microsecond, @d);

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

Year        2021-01-01 00:00:00.0000000
Quarter     2021-10-01 00:00:00.0000000
Month       2021-12-01 00:00:00.0000000
Week        2021-12-05 00:00:00.0000000
Iso_week    2021-12-06 00:00:00.0000000
DayOfYear   2021-12-08 00:00:00.0000000
Day         2021-12-08 00:00:00.0000000
Hour        2021-12-08 11:00:00.0000000
Minute      2021-12-08 11:30:00.0000000
Second      2021-12-08 11:30:15.0000000
Millisecond 2021-12-08 11:30:15.1230000
Microsecond 2021-12-08 11:30:15.1234560

B. @@DATEFIRST の設定

次の例は、week datepart で@@DATEFIRST設定を使用する方法を示しています

DECLARE @d datetime2 = '2021-11-11 11:11:11.1234567';

SELECT 'Week-7', DATETRUNC(week, @d); -- Uses the default DATEFIRST setting value of 7 (U.S. English)

SET DATEFIRST 6;
SELECT 'Week-6', DATETRUNC(week, @d);

SET DATEFIRST 3;
SELECT 'Week-3', DATETRUNC(week, @d);

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

Week-7  2021-11-07 00:00:00.0000000
Week-6  2021-11-06 00:00:00.0000000
Week-3  2021-11-10 00:00:00.0000000

C: 日付リテラル

次の例は、date パラメーター リテラルの使用法を示しています。

SELECT DATETRUNC(month, '1998-03-04');

SELECT DATETRUNC(millisecond, '1998-03-04 10:10:05.1234567');

DECLARE @d1 char(200) = '1998-03-04';
SELECT DATETRUNC(millisecond, @d1);

DECLARE @d2 nvarchar(max) = '1998-03-04 10:10:05';
SELECT DATETRUNC(minute, @d2);

結果セットは次のとおりです。 結果はすべて datetime2(7)型です。

1998-03-01 00:00:00.0000000
1998-03-04 10:10:05.1230000
1998-03-04 00:00:00.0000000
1998-03-04 10:10:00.0000000

D. 変数と date パラメーター

次の例は、date パラメーターの使用法を示しています。

DECLARE @d datetime2 = '1998-12-11 02:03:04.1234567';
SELECT DATETRUNC(day, @d);

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

1998-12-11 00:00:00.0000000

E. 列と date パラメーター

Sales.CustomerTransactions テーブルの TransactionDate 列は、date パラメーターの column 引数の例として機能します。

USE WideWorldImporters;
GO

SELECT CustomerTransactionID,
    DATETRUNC(month, TransactionDate) AS MonthTransactionOccurred,
    InvoiceID,
    CustomerID,
    TransactionAmount,
    SUM(TransactionAmount) OVER (
        PARTITION BY CustomerID ORDER BY TransactionDate,
            CustomerTransactionID ROWS UNBOUNDED PRECEDING
        ) AS RunningTotal,
    TransactionDate AS ActualTransactionDate
FROM [WideWorldImporters].[Sales].[CustomerTransactions]
WHERE InvoiceID IS NOT NULL
    AND DATETRUNC(month, TransactionDate) >= '2015-12-01';

F. 式と date パラメーター

date パラメーターは、T-SQL の日時型に解決できる式、または datetime2(7) に解決できる任意の文字列リテラルを受け取ります。 Sales.CustomerTransactions テーブルの TransactionDate 列は、date パラメーターに対する "式" の使用を例示するための架空の引数として機能します。

SELECT DATETRUNC(m, SYSDATETIME());

SELECT DATETRUNC(yyyy, CONVERT(date, '2021-12-1'));

USE WideWorldImporters;
GO
SELECT DATETRUNC(month, DATEADD(month, 4, TransactionDate))
FROM Sales.CustomerTransactions;
GO

G. 最大精度を表す datepart に date を切り詰める

datepart の単位最大精度が入力日時型と同じ場合、この datepart への入力日時の切り詰めでは何も行われません。

例 1

DECLARE @d datetime = '2015-04-29 05:06:07.123';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);

結果セットは次のとおりです。 入力 datetime と切り捨てられた date パラメーターは同じです。

Input     2015-04-29 05:06:07.123
Truncated 2015-04-29 05:06:07.123

例 2

DECLARE @d date = '2050-04-04';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(day, @d);

結果セットは次のとおりです。 入力 datetime と切り捨てられた date パラメーターは同じです。

Input     2050-04-04
Truncated 2050-04-04

例 3: smalldatetime の精度

smalldatetime は、秒のフィールドを含みますが、精度は最も近い分までです。 したがって、最も近い分または最も近い秒に切り詰めると、何も行われません。

DECLARE @d smalldatetime = '2009-09-11 12:42:12'
SELECT 'Input', @d;
SELECT 'Truncated to minute', DATETRUNC(minute, @d)
SELECT 'Truncated to second', DATETRUNC(second, @d);

結果セットは次のとおりです。 入力 smalldatetime 値は、切り捨てられた両方の値と同じです。

Input                2009-09-11 12:42:00
Truncated to minute  2009-09-11 12:42:00
Truncated to second  2009-09-11 12:42:00

例 4: datetime の精度

datetime の精度は、最小でも 3.33 ミリ秒までです。 そのため、 datetime をミリ秒に切り捨てると、ユーザーが期待する結果とは異なる結果が得られる場合があります。 ただし、この切り詰められた値は、内部的に格納されている datetime 値と同じです。

DECLARE @d datetime = '2020-02-02 02:02:02.002';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);

結果セットは次のとおりです。 切り捨てられた date は、格納されている dateと同じです。 これは、 DECLARE ステートメントに基づいて想定される内容とは異なる場合があります。

Input     2020-02-02 02:02:02.003
Truncated 2020-02-02 02:02:02.003

解説

date切り捨てが、そのデータ型でサポートされている最小日付より前の日付にバックトラックしようとすると、エラーがスローされます。 このエラーは、 week datepart を使用する場合にのみ発生します。 iso_week datepartを使用する場合は発生しません。T-SQL のすべての日付型は、その最小日付に月曜日を使用するためです。 対応する結果のエラー メッセージの例を次に示します。

DECLARE @d date= '0001-01-01 00:00:00';
SELECT DATETRUNC(week, @d);

Msg 9837, Level 16, State 3, Line 84
An invalid date value was encountered: The date value is less than the minimum date value allowed for the data type.

DATETRUNC関数または入力日付データ型が使用datepartをサポートしていない場合、DATEPART エラーがスローされます。 このエラーは、以下のような場合に発生します。

1. DATETRUNC でサポートされていない datepart が使われています (つまり、weekday、tzoffset、または nanosecond)

2. time 関連の datepart が date データ型で使われているか、または date 関連の datepart が time データ型で使われています。 対応する結果のエラー メッセージの例を次に示します。

   DECLARE @d time = '12:12:12.1234567';
   SELECT DATETRUNC(year, @d);
   

   Msg 9810, Level 16, State 10, Line 78
   The datepart year is not supported by date function datetrunc for data type time.
   

3. datepartでは、データ型がサポートするよりも小数部の時間スケール精度が必要です。 詳細については、「 Fractional time scale precision」を参照してください。 対応する結果のエラー メッセージの例を次に示します。

   DECLARE @d datetime2(3) = '2021-12-12 12:12:12.12345';
   SELECT DATETRUNC(microsecond, @d);
   

   Msg 9810, Level 16, State 11, Line 81
   The datepart microsecond is not supported by date function datetrunc for data type datetime2.
   

関連するコンテンツ

• @@DATEFIRST (Transact-SQL)
• DATEPART (Transact-SQL)
• 日付と時刻のデータ型および関数 (Transact-SQL)