DATETRUNC (Transact-SQL)
適用対象:
SQL Server 2022 (16.x)Azure SQL DatabaseAzure SQL Managed InstanceMicrosoft 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) です。 したがって、milliseconddatepart に切り詰めるには、小数部の時間スケールが少なくとも 3 である必要があります。 同様に、microseconddatepart に切り詰めるには、小数部の時間スケールが少なくとも 6 である必要があります。 T-SQL の日時型では小数部時間スケール 9 はサポートされていないため、DATETRUNC では nanoseconddatepart はサポートされません。
例
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 の設定
次の例は、weekdatepart での @@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 エラーがスローされます。 これは、weekdatepart を使ったときにのみ発生します。 T-SQL のすべての日時型では一貫して最小曜日に月曜日が使われるため、iso_weekdatepart を使ったときは発生しません。 対応する結果のエラー メッセージの例を次に示します。
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.
関連項目
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示