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 錯誤。 這種情況的發生原因包括：

1. 使用 DATETRUNC 不支援的 datepart (即 weekday、tzoffset 或 nanosecond)

2. 搭配日期資料類型使用時間相關的 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.
   

3. 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.
   

另請參閱

• @@DATEFIRST (Transact-SQL)
• DATEPART (Transact-SQL)
• 日期和時間資料類型與函數 (Transact-SQL)