DATETRUNC (Transact-SQL)

適用于：SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL 受控執行個體

從 SQL Server 2022 (16.x) 開始，此函式會將輸入日期截斷為指定的datepart。

語法

DATETRUNC ( datepart, date )

引數

datepart

指定截斷的有效位數。 此資料表會列出 的所有有效 datepart 值 DATETRUNC ，因為它也是輸入日期類型的有效部分。

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

注意

不支援 weekday、 timezoneoffset和 nanosecond T-SQL dateparts 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。 DATETRUNC不支援 nanoseconddatepart，因為沒有 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. 日期常值

下列範例說明 如何使用日期 參數常值：

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 參數：

DECLARE @d datetime2 = '1998-12-11 02:03:04.1234567';
SELECT DATETRUNC(day, @d);

結果如下︰

1998-12-11 00:00:00.0000000

E. 資料行和 日期 參數

TransactionDate資料表中的資料 Sales.CustomerTransactions 行可作為date參數的範例資料行引數：

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參數接受任何可解析為 T-SQL 日期類型的運算式，或任何可解析為datetime2 (7) 的字串常值。 TransactionDate資料表中的資料 Sales.CustomerTransactions 行可作為人工引數，以示範對 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的日期，代表其最大精確度

如果 datepart 與輸入日期類型具有相同的單位最大精確度，則截斷此 datepart 的輸入日期不會有任何作用。

範例 1

DECLARE @d datetime = '2015-04-29 05:06:07.123';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);

以下是結果集，說明輸入 datetime 和截斷 日期 參數相同：

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 和截斷 日期 參數相同：

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 precision

datetime 只會精確到 3.33 毫秒。 因此，將 日期時間 截斷為毫秒可能會產生與使用者預期不同的結果。 不過，這個截斷的值與內部儲存 的日期時間 值相同。

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

備註

DATE TOO SMALL如果日期截斷嘗試回復到該資料類型所支援之最低日期之前的日期，就會擲回錯誤。 這只有在使用 weekdatepart時才會發生。 使用 iso_weekdatepart時無法發生，因為所有 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. 不支援的 DATETRUNCdatepart (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) (日期和時間資料類型和函式