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

zxrhhm

于 2024-07-26 10:41:52 发布

阅读量998

点赞数 39

文章标签： sqlserver

本文链接：https://blog.csdn.net/zxrhhm/article/details/140699765

版权

1、本文内容

语法
参数
返回类型
小数位时间刻度精度
示例
注解
请参阅

适用于：

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

参考官方文档地址
https://learn.microsoft.com/en-us/sql/t-sql/functions/datetrunc-transact-sql?view=sql-server-ver16

2、语法

DATETRUNC ( datepart, date )

3、参数

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) 的（任何字符串类型的）字符串字面量。

4、返回类型

DATETRUNC 的返回数据类型是动态的。 DATETRUNC 返回日期类型与输入日期相同的截断日期（如果适用，还会返回小数位时间刻度相同的截断日期）。例如，如果向 DATETRUNC 提供了 datetimeoffset(3) 输入日期，它将返回 datetimeoffset(3)。如果提供的字符串字面量可以解析为 datetime2(7)，则 DATETRUNC 将返回 datetime2(7)。

5、小数位时间刻度精度

毫秒的小数位时间刻度为 3 (.123)，微秒的小数位时间刻度为 6 (.123456)，纳米秒的小数位时间刻度为 9 (.123456789)。 time、datetime2 和 datetimeoffset 数据类型允许的最大小数位时间刻度为 7 (.1234567)。因此，若要截断到millisecond日期部分，小数时间刻度必须至少为 3。同样，若要截断到microsecond日期部分，小数时间刻度必须至少为 6。 DATETRUNC不支持 datepart，nanosecond因为 T-SQL 日期类型不支持 9 的小数级时间刻度。

6、示例

6.1、使用不同的 datepart 选项

以下示例演示了各种 datepart 选项的使用：


DECLARE @d datetime2 = '2024-07-25 21:00:51.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 		2024-01-01 00:00:00.0000000

Quarter 	2024-07-01 00:00:00.0000000

Month 		2024-07-01 00:00:00.0000000

Week 		2024-07-24 00:00:00.0000000

Iso_week 	2024-07-22 00:00:00.0000000

DayOfYear 	2024-07-25 00:00:00.0000000

Day  		2024-07-25 00:00:00.0000000

Hour 		2024-07-25 21:00:00.0000000

Minute 		2024-07-25 21:00:00.0000000

Second 		2024-07-25 21:00:51.0000000

Millisecond 2024-07-25 21:00:51.1230000

Microsecond 2024-07-25 21:00:51.1234560

6.2、@@DATEFIRST 设置

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

DECLARE @d datetime2 = '2024-07-25 21:00:51';

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 2024-07-24 00:00:00.0000000

Week-6 2024-07-20 00:00:00.0000000

Week-3 2024-07-24 00:00:00.0000000

6.3、日期文字

以下示例演示了 date 参数文本的用法：

SELECT DATETRUNC(month, '2024-07-25');

SELECT DATETRUNC(millisecond, '2024-07-25 10:10:05.1234567');

DECLARE @d1 char(200) = '2024-07-25';
SELECT DATETRUNC(millisecond, @d1);

DECLARE @d2 nvarchar(max) = '2024-07-25 10:10:05';
SELECT DATETRUNC(minute, @d2);

下面是结果集（所有结果的类型为 datetime2(7)）：

2024-07-01 00:00:00.0000000

2024-07-25 10:10:05.1230000

2024-07-25 00:00:00.0000000

2024-07-25 10:10:00.0000000

6.3、变量和 date 参数

下面的示例说明了 date 参数的用法：

DECLARE @d datetime2 = '2024-07-26 02:03:04.1234567';
SELECT DATETRUNC(day, @d);

结果

2024-07-26 00:00:00.0000000

6.4、列和 date 参数

T_ScrapReason表中的 ModifiedDate 列用作 date 参数的示例 column 自变量

SELECT ID,UnitMeasureCode,NameInfo,ModifiedDate,DATETRUNC(MONTH,ModifiedDate) AS new_date FROM T_ScrapReason；

SELECT ID,UnitMeasureCode,NameInfo,ModifiedDate,DATETRUNC(MONTH,ModifiedDate) AS new_date FROM T_ScrapReason
WHERE DATETRUNC(MONTH,ModifiedDate)>='2024-08-01'；

在这里插入图片描述

6.5、表达式和 date 参数

date 参数接受可以解析为 T-SQL 日期类型的任何表达式，或者可以解析为 datetime2(7) 的任何字符串字面量。 T_ScrapReason 表中的 ModifiedDate 列充当一个假自变量，用于演示如何在表达式中使用 date 参数

SELECT DATETRUNC(m, SYSDATETIME());

SELECT DATETRUNC(yyyy, CONVERT(date, '2024-7-1'));

SELECT DATETRUNC(MONTH, DATEADD(MONTH, 4, ModifiedDate ))
FROM T_ScrapReason;
GO

在这里插入图片描述

6.6、将日期截断到表示其最大精度的 datepart

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

6.6.1、millisecond 精度

DECLARE @d datetime = '2024-07-26 05:06:07.123';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);

在这里插入图片描述

6.6.2、day 精度

DECLARE @d date = '2024-07-04';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(day, @d);

在这里插入图片描述

6.6.3、smalldatetime 精度

smalldatetime 仅精确到最接近的分钟，即使它有一个秒钟字段。因此，将其截断到最接近的分钟或最接近的秒将不起作用。

DECLARE @d smalldatetime = '2024-08-01 12:42:12'
SELECT 'Input', @d;
SELECT 'Truncated to minute', DATETRUNC(minute, @d)
SELECT 'Truncated to second', DATETRUNC(second, @d);

7、注解

如果日期截断尝试回溯到该日期类型支持的最小日期之前的日期，则会引发 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）
与 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.

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.

8、请参阅

@@DATEFIRST (Transact-SQL)
https://learn.microsoft.com/en-us/sql/t-sql/functions/datefirst-transact-sql?view=sql-server-ver16

DATEPART (Transact-SQL)
https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver16

日期和时间数据类型及函数 (Transact-SQL)
https://learn.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-ver16