DATETRUNC (Transact-SQL)

適用於：Microsoft Fabric Microsoft網狀架構倉儲中的 SQL Server 2022 （16.x） Azure SQL 資料庫 Azure SQL 受控執行個體 SQL 分析端點

函DATETRUNC式會傳回截斷至指定 datepart 的輸入日期。

注意

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 中，一週的第一天是由 @@DATEFIRST T-SQL 設定所定義。 針對美國英文環境， @@DATEFIRST 預設為 7 （星期日）。
iso_week	isowk, isoww	截斷為 ISO 周的第一天。 在 ISO8601 日曆系統中，一週的第一天是星期一。
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs

注意

不支援DATETRUNC工作日、時區offset 和 nanosecond T-SQL dateparts。

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;
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. 將 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 值相同。

DECLARE @d datetime = '2020-02-02 02:02:02.002';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);

結果集如下所示。 截斷 日期 與預 存日期相同。 這可能與您根據 DECLARE 語句所預期的不同。

Input     2020-02-02 02:02:02.003
Truncated 2020-02-02 02:02:02.003

備註

如果 日期 截斷嘗試回溯到該數據類型所支援之最小日期之前的日期，就會擲回錯誤。 只有在使用 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，就會擲回錯誤。 當下列情況時，可能會發生此錯誤：

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)