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 の値の一覧です。入力の日時型でも有効な部分であるとします。
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 |
注意
T-SQL の datepart の weekday、timezoneoffset、nanosecond は、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 以上にする必要があります。 DATETRUNC
T-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;
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 を 1 ミリ秒に切り詰めると、ユーザーの予想とは異なる結果になる場合があります。 ただし、この切り詰められた値は、内部的に格納されている 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 の切り詰めが、そのデータ型でサポートされている最小日時より前の日付にバックトラックしようとすると、DATE TOO SMALL
エラーがスローされます。 これは、 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.
使われている datepart が DATETRUNC
関数または入力日時データ型でサポートされていない場合、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 で、データ型によってサポートされているものより高い小数部時間スケールの精度が必要です (小数部の時間スケールの精度に関する記事を参照してください)。 対応する結果のエラー メッセージの例を次に示します。
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.
関連項目
フィードバック
https://aka.ms/ContentUserFeedback」を参照してください。
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「フィードバックの送信と表示