DATETRUNC (Transact-SQL)

适用于： sql Server 2022 （16.x） Azure SQL 数据库 Azure SQL 托管实例 Microsoft Fabric Warehouse 中的 Microsoft Fabric Warehouse 中的 SQL 分析终结点

DATETRUNC 函数返回截断到指定日期部分的输入日期。

语法

DATETRUNC ( datepart , date )

参数

datepart

指定截断的精度。 下表列出 DATETRUNC 的所有有效 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日期部分，小数时间刻度必须至少为 3。 同样，若要截断到microsecond日期部分，小数时间刻度必须至少为 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 设置

以下示例演示如何 @@DATEFIRST 将设置与 week datepart 配合使用：

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. 将日期截断到表示其最大精度的 datepart

如果 datepart 的单位最大精度与输入日期类型相同，则将输入日期截断到此 datepart 将不起作用。

示例 1

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

下面是结果集： 输入 日期/时间 和截断 日期 参数相同。

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);

下面是结果集： 输入 日期/时间 和截断 日期 参数相同。

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

3. 日期 部分 需要比数据类型支持的更小数部分时间刻度精度。 有关详细信息，请参阅 分数时间刻度精度。 下面是一个包含相应结果错误消息的示例：

   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)