Date_Bucket (Transact-SQL)Date_Bucket (Transact-SQL)

此函数从 origin 参数定义的时间戳或默认原始值 1900-01-01 00:00:00.000(如果未指定 origin 参数)返回与每个日期/时间 Bucket 的起始值相对应的日期/时间值。This function returns the datetime value corresponding to the start of each datetime bucket, from the timestamp defined by the origin parameter or the default origin value of 1900-01-01 00:00:00.000 if the origin parameter is not specified.

有关所有 Transact-SQL 日期和时间数据类型及函数的概述,请参阅日期和时间数据类型及函数 (Transact-SQL)See Date and Time Data Types and Functions (Transact-SQL) for an overview of all Transact-SQL date and time data types and functions.

Transact-SQL 语法约定Transact-SQL Syntax Conventions

语法Syntax

DATE_BUCKET (datePart, number, date, origin)

参数Arguments

datePartdatePart

与“number”参数一起使用的 date 的一部分。The part of date that is used with the ‘number’ parameter. 例如:Ex. 年、月、分钟、秒等。Year, month, minute, second etc.

备注

对于 datepPart 参数,DATE_BUCKET 不接受用户定义的变量等效项。DATE_BUCKET does not accept user-defined variable equivalents for the datepPart arguments.

datePartdatePart 缩写形式Abbreviations
dayday dd, d dd, d
week week wk, ww wk, ww
hourhour hhhh
minute minute mi, n mi, n
second second ss, s ss, s
millisecond millisecond msms

数字number

一个整数,用于确定与 datePart 参数组合使用的存储桶的宽度。The integer number that decides the width of the bucket combined with datePart argument. 这表示从原始时间开始的 dataPart 存储桶的宽度。This represents the width of the dataPart buckets from the origin time. This argument cannot be a negative integer value.This argument cannot be a negative integer value.

datedate

可解析为下列值之一的表达式:An expression that can resolve to one of the following values:

  • datedate
  • datetimedatetime
  • datetimeoffsetdatetimeoffset
  • datetime2datetime2
  • smalldatetimesmalldatetime
  • timetime

对于 date,DATE_BUCKET 将接受列表达式、表达式或用户定义的变量,前提是它们解析为上面提到的任何数据类型。For date, DATE_BUCKET will accept a column expression, expression, or user-defined variable if they resolve to any of the data types mentioned above.

Origin

可解析为下列值之一的可选表达式:An optional expression that can resolve to one of the following values:

  • datedate
  • datetimedatetime
  • datetimeoffsetdatetimeoffset
  • datetime2datetime2
  • smalldatetimesmalldatetime
  • timetime

Origin 的数据类型应与 Date 参数的数据类型相匹配。The data type for Origin should match the data type of the Date parameter.

如果没有为该函数指定原始值,DATE_BUCKET 将使用默认原始日期值 1900-01-01 00:00:00.000,即 1900 年 1 月 1 日星期一上午 12:00。DATE_BUCKET uses a default origin date value of 1900-01-01 00:00:00.000 i.e. 12:00 AM on Monday, January 1 1900, if no Origin value is specified for the function.

返回类型Return Type

此方法的返回值数据类型是动态的。The return value data type for this method is dynamic. 返回类型取决于为 date 提供的参数。The return type depends on the argument supplied for date. 如果为 date 提供了有效的输入数据类型,则 DATE_BUCKET 返回相同的数据类型。If a valid input data type is supplied for date, DATE_BUCKET returns the same data type. 如果为 date 参数指定了字符串文本,则 DATE_BUCKET 会引发错误。DATE_BUCKET raises an error if a string literal is specified for the date parameter.

返回值Return Values

了解来自 DATE_BUCKET 的输出Understanding the output from DATE_BUCKET

Date_Bucket 返回与 datePart 和 number 参数相对应的最晚日期或时间值。Date_Bucket returns the latest date or time value, corresponding to the datePart and number parameter. 例如,在下面的表达式中,Date_Bucket 将返回输出值 2020-04-13 00:00:00.0000000,因为输出是基于从默认原始时间 1900-01-01 00:00:00.000 开始的一周存储桶计算得出的。For example, in the expressions below, Date_Bucket will return the output value of 2020-04-13 00:00:00.0000000, as the output is calculated based on one week buckets from the default origin time of 1900-01-01 00:00:00.000. 2020-04-13 00:00:00.0000000 值是从原始值 1900-01-01 00:00:00.000 开始的 6276 周。The value 2020-04-13 00:00:00.0000000 is 6276 weeks from the origin value of 1900-01-01 00:00:00.000.

declare @date datetime2 = '2020-04-15 21:22:11'
Select DATE_BUCKET(wk, 1, @date)

对于下面的所有表达式,将返回相同的输出值 2020-04-13 00:00:00.0000000For all the expressions below, the same output value of 2020-04-13 00:00:00.0000000 will be returned. 这是因为 2020-04-13 00:00:00.0000000 是从原始日期开始的 6276 周,6276 可被2、3、4 和 6 整除。This is because 2020-04-13 00:00:00.0000000 is 6276 weeks from the origin date and 6276 is divisible by 2, 3, 4 and 6.

declare @date datetime2 = '2020-04-15 21:22:11'
Select DATE_BUCKET(wk, 2, @date)
Select DATE_BUCKET(wk, 3, @date)
Select DATE_BUCKET(wk, 4, @date)
Select DATE_BUCKET(wk, 6, @date)

以下表达式的输出为 2020-04-06 00:00:00.0000000,即,从默认原始时间 1900-01-01 00:00:00.000 开始的 6275 周。The output for the expression below is 2020-04-06 00:00:00.0000000, which is 6275 weeks from the default origin time 1900-01-01 00:00:00.000.

declare @date datetime2 = '2020-04-15 21:22:11'
Select DATE_BUCKET(wk, 5, @date)

以下表达式的输出为 2020-06-09 00:00:00.0000000,即,从指定原始时间 2019-01-01 00:00:00 开始的 75 周。The output for the expression below is 2020-06-09 00:00:00.0000000 , which is 75 weeks from the specified origin time 2019-01-01 00:00:00.

declare @date datetime2 = '2020-06-15 21:22:11'
declare @origin datetime2 = '2019-01-01 00:00:00'
Select DATE_BUCKET(wk, 5, @date, @origin)

datepart 参数datepart Argument

dayofyear、day 和 weekday 返回相同的值 。dayofyear, day, and weekday return the same value. 每个 datepart 及其缩写都返回相同的值。Each datepart and its abbreviations return the same value.

number 参数number Argument

number 参数不能超出正 int 值的范围。The number argument cannot exceed the range of positive int values. 在以下语句中,number 的参数超出 int 的范围(超出 1)。In the following statements, the argument for number exceeds the range of int by 1. 以下语句返回以下错误消息:“Msg 8115, Level 16, State 2, Line 2. Arithmetic overflow error converting expression to data type int."The following statement returns the following error message: "Msg 8115, Level 16, State 2, Line 2. Arithmetic overflow error converting expression to data type int."

declare @date datetime2 = '2020-04-30 00:00:00'
Select DATE_BUCKET(dd, 2147483648, @date)

如果 number 的负值传递到 Date_Bucket 函数,将返回以下错误。If a negative value for number is passed to the Date_Bucket function, the following error will be returned.

Msg 9834, Level 16, State 1, Line 1
Invalid bucket width value passed to date_bucket function. Only positive values are allowed.

date 参数date Argument

DATE_BUCKET 返回与 date 参数的数据类型相对应的基值。DATE_BUCKET return the base value corresponding to the data type of the date argument. 在下面的示例中,将返回具有 datetime2 数据类型的输出值。In the following example, an output value with datetime2 datatype is returned.

Select DATE_BUCKET(dd, 10, SYSUTCDATETIME())

origin 参数origin Argument

origindate 参数的数据类型必须相同。The data type of the origin and date arguments in must be the same. 如果使用不同的数据类型,则会生成错误。If different data types are used, an error will be generated.

备注Remarks

在以下子句中使用 DATE_BUCKETUse DATE_BUCKET in the following clauses:

  • GROUP BYGROUP BY
  • HAVINGHAVING
  • ORDER BYORDER BY
  • SELECT <list>SELECT <list>
  • WHEREWHERE

示例Examples

A.A. 从原始时间开始计算存储桶宽度为 1 的 Date_BucketCalculating Date_Bucket with a bucket width of 1 from the origin time

其中每个语句都将从原始时间开始增加存储桶宽度为 1 的 date_bucket:Each of these statements increments date_bucket with a bucket width of 1 from the origin time:

declare @date datetime2 = '2020-04-30 21:21:21'
Select 'Week',  DATE_BUCKET(wk, 1, @date)
Union All
Select 'Day',  DATE_BUCKET(dd, 1, @date)
Union All
Select 'Hour',  DATE_BUCKET(hh, 1, @date)
Union All
Select 'Minutes',  DATE_BUCKET(mi, 1, @date)
Union All
Select 'Seconds',  DATE_BUCKET(ss, 1, @date)

下面是结果集:Here is the result set.

Week    2020-04-27 00:00:00.0000000
Day     2020-04-30 00:00:00.0000000
Hour    2020-04-30 21:00:00.0000000
Minutes 2020-04-30 21:21:00.0000000
Seconds 2020-04-30 21:21:21.0000000

B.B. 使用表达式作为 number 和 date 形参的实参Using expressions as arguments for the number and date parameters

以下示例使用不同类型的表达式作为 number 和 date 形参的实参 。These examples use different types of expressions as arguments for the number and date parameters. 这些示例是使用“AdventureWorksDW2017”数据库生成的。These examples are built using the 'AdventureWorksDW2017' Database.

将用户定义的变量指定为 number 和 dateSpecifying user-defined variables as number and date

此示例将用户定义的变量指定为 number 和 date 的参数 :This example specifies user-defined variables as arguments for number and date:

DECLARE @days int = 365,
        @datetime datetime2 = '2000-01-01 01:01:01.1110000'; /* 2000 was a leap year */;  
SELECT Date_Bucket(day, @days, @datetime);

下面是结果集:Here is the result set.

---------------------------
1999-12-08 00:00:00.0000000

(1 row affected)

将一列指定为 dateSpecifying a column as date

在下面的示例中,我们将计算基于每周日期存储桶分组的 OrderQuantity 的总和及 UnitPrice 的总和。In the example below, we are calculating the sum of OrderQuantity and sum of UnitPrice grouped over weekly date buckets.

SELECT
    Date_Bucket(week, 1 ,cast(Shipdate as datetime2)) AS ShippedDateBucket
    ,Sum(OrderQuantity)  As SumOrderQuantity
    ,Sum(UnitPrice) As SumUnitPrice
FROM dbo.FactInternetSales FIS
where Shipdate between '2011-01-03 00:00:00.000' and '2011-02-28 00:00:00.000'
Group by Date_Bucket(week, 1 ,cast(Shipdate as datetime2))
order by 1

下面是结果集:Here is the result set.

ShippedDateBucket           SumOrderQuantity SumUnitPrice
--------------------------- ---------------- ---------------------
2011-01-03 00:00:00.0000000 21               65589.7546
2011-01-10 00:00:00.0000000 27               89938.5464
2011-01-17 00:00:00.0000000 31               104404.9064
2011-01-24 00:00:00.0000000 36               118525.6846
2011-01-31 00:00:00.0000000 39               123555.431
2011-02-07 00:00:00.0000000 35               109342.351
2011-02-14 00:00:00.0000000 32               107804.8964
2011-02-21 00:00:00.0000000 37               119456.3428
2011-02-28 00:00:00.0000000 9                28968.6982

将标量系统函数指定为 dateSpecifying scalar system function as date

此示例指定 SYSDATETIME 为 date 。This example specifies SYSDATETIME for date. 返回的确切值取决于语句执行的日期和时间:The exact value returned depends on the day and time of statement execution:

SELECT Date_Bucket(wk, 10, SYSDATETIME());  

下面是结果集:Here is the result set.

---------------------------
2020-03-02 00:00:00.0000000

(1 row affected)

将标量子查询和标量函数指定为 number 和 dateSpecifying scalar subqueries and scalar functions as number and date

此示例使用标量子查询 MAX(OrderDate) 作为 number 和 date 的参数 。This example uses scalar subqueries, MAX(OrderDate), as arguments for number and date. (SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100) 充当 number 形参的假实参,用来说明如何从值列表中选择 number 实参 。(SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100) serves as an artificial argument for the number parameter, to show how to select a number argument from a value list.

SELECT DATE_BUCKET(week,(SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100),  
    (SELECT MAX(OrderDate) FROM dbo.FactInternetSales));  

将数值表达式和标量系统函数指定为 number 和 dateSpecifying numeric expressions and scalar system functions as number and date

此示例使用数值表达式 ((10/2)),和标量系统函数 (SYSDATETIME) 作为 number 和 date 的参数。This example uses a numeric expression ((10/2)), and scalar system functions (SYSDATETIME) as arguments for number and date.

SELECT Date_Bucket(week,(10/2), SYSDATETIME());

将聚合开窗函数指定为 numberSpecifying an aggregate window function as number

此示例使用聚合开窗函数作为 number 的参数 。This example uses an aggregate window function as an argument for number.

Select 
    DISTINCT DATE_BUCKET(day, 30, Cast([shipdate] as datetime2)) as DateBucket,
    First_Value([SalesOrderNumber]) OVER (Order by DATE_BUCKET(day, 30, Cast([shipdate] as datetime2))) as First_Value_In_Bucket,
    Last_Value([SalesOrderNumber]) OVER (Order by DATE_BUCKET(day, 30, Cast([shipdate] as datetime2))) as Last_Value_In_Bucket
    from [dbo].[FactInternetSales]
Where ShipDate between '2011-01-03 00:00:00.000' and '2011-02-28 00:00:00.000'
order by DateBucket
GO  

C.C. 使用非默认原始值Using a non default origin value

本示例使用非默认原始值来生成日期 Bucket。This example uses a non default orgin value to generate the date buckets.

declare @date datetime2 = '2020-06-15 21:22:11'
declare @origin datetime2 = '2019-01-01 00:00:00'
Select DATE_BUCKET(hh, 2, @date, @origin)

另请参阅See also

CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)