DATETRUNC (Transact-SQL)
適用於: sql Server 2022 (16.x) Azure SQL 資料庫 Azure SQL 受控執行個體 Microsoft Fabric 中 Microsoft Fabric 倉儲中的 SQL 分析端點
函DATETRUNC
式會傳回截斷至指定 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 中,一週的第一天是由 @@DATEFIRST T-SQL 設定所定義。 針對美國英文環境,@@DATEFIRST 預設為 7 (星期日)。 |
iso_week | isowk、isoww | 截斷至 ISO 週的第一天。 在 ISO8601 日曆系統中,一週的第一天是星期一。 |
hour | hh | |
minute | mi, n | |
second | ss, s | |
millisecond | ms | |
microsecond | mcs |
注意
DATETRUNC
不支援 weekday、timezoneoffset 和 nanosecond T-SQL 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。 DATETRUNC
不支援 datepart,nanosecond
因為沒有 T-SQL 日期類型支援 9 的小數時間刻度。
範例
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 設定
下列範例說明搭配 datepart 使用 @@DATEFIRST
設定week
:
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. 將 date 截斷至代表其最大有效位數的 datepart
如果 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 截斷嘗試回溯至該資料類型所支援最小日期之前的日期,就會擲回 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.
如果 DATETRUNC
函數或輸入日期資料類型不支援所使用的 datepart,則會擲回 DATEPART
錯誤。 這種情況的發生原因包括:
使用
DATETRUNC
不支援的 datepart (即weekday
、tzoffset
或nanosecond
)搭配日期資料類型使用時間相關的 datepart,或搭配時間資料類型使用日期相關的 datepart。 以下示範對應的結果錯誤訊息:
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 問題作為內容的意見反應機制,並將它取代為新的意見反應系統。 如需詳細資訊,請參閱:提交並檢視相關的意見反應