DATETRUNC (Transact-SQL)
適用対象: 
SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance Microsoft Fabric のSQL 分析エンドポイント Microsoft Fabric のウェアハウス
DATETRUNC 関数は、入力の date を指定した datepart に切り詰めて返します。
Note
DATETRUNC は SQL Server 2022 (16.x) で導入されました。
構文
DATETRUNC ( datepart , date )
引数
datepart
切り詰めの精度を指定します。 次の表は、DATETRUNC に対して有効なすべての datepart の値の一覧です。入力の日時型でも有効な部分であるとします。
| datepart | 省略形 | 切り詰めに関する注意 |
|---|---|---|
year |
yy, yyyy |
|
quarter |
qq, q |
|
month |
mm, m |
|
dayofyear |
dy, y |
dayofyear と同じ方法で切り捨てられます。 day |
day |
dd, d |
day と同じ方法で切り捨てられます。 dayofyear |
week |
wk, ww |
週の最初の曜日に切り詰めます。 T-SQL では、週の最初の曜日は T-SQL の設定 @@DATEFIRST によって定義されます。 米国英語環境の場合、 @@DATEFIRST の既定値は 7 (日曜日) です。 |
iso_week |
isowk, isoww |
ISO 週の最初の日に切り捨てます。 ISO8601 カレンダー システムでの週の最初の曜日は月曜日です。 |
hour |
hh |
|
minute |
mi, n |
|
second |
ss, s |
|
millisecond |
ms |
|
microsecond |
mcs |
Note
weekday、timezoneoffset、および nanosecond T-SQL dateparts はDATETRUNCではサポートされていません。
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 エラーがスローされます。 このエラーは、以下のような場合に発生します。
DATETRUNCでサポートされていない datepart が使われています (つまり、weekday、tzoffset、またはnanosecond)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.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.