DATE_BUCKET (Transact-SQL)
適用於：
SQL Server 2022 （16.x） Azure SQL 資料庫 Azure SQL 受控執行個體 Azure SQL Edge
此函式會從 origin 參數所定義的時間戳記 (如果未指定 origin 參數，則會從 1900-01-01 00:00:00.000 的預設原始值)，傳回對應至每個日期時間值區起點的日期時間值。
如需所有 Transact-SQL 日期和時間資料類型與函式的概觀，請參閱日期和時間資料類型與函式 (Transact-SQL)。
語法
DATE_BUCKET (datepart, number, date [, origin ] )
引數
datepart
與 number 參數搭配使用的 date 部分，例如 year、month、day、minute、second。
DATE_BUCKET 不會接受 datepart 引數的使用者定義變數對等項目。
| datepart | 縮寫 |
|---|---|
| day | dd, d |
| week | wk, ww |
| month | mm, m |
| quarter | qq, q |
| year | yy, yyyy |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
number
決定值區寬度並與 datePart 引數結合的整數。 這代表從原始時間起算的 datepart 值區寬度。 這個引數不能是負整數值。
date
可解析成下列其中一個值的運算式：
- date
- datetime
- datetime2
- datetimeoffset
- smalldatetime
- time
針對 date，DATE_BUCKET 會接受資料行運算式、運算式或使用者定義的變數，前提是其必須解析為上述任何資料類型。
origin
可解析成下列其中一個值的選擇性運算式：
- date
- datetime
- datetime2
- datetimeoffset
- smalldatetime
- time
origin 的資料類型應符合 date 參數的資料類型。
如果未針對函式指定任何 origin 值，則 DATE_BUCKET 會使用 1900-01-01 00:00:00.000 的預設原始日期值，也就是 1900 年 1 月 1 日星期一上午 12:00。
傳回類型
這個方法的傳回值資料類型為動態。 傳回型別取決於提供給 date 的引數而定。 如果提供有效的輸入資料類型給 date，DATE_BUCKET 便會傳回相同的資料類型。 如果為 date 參數指定了字串常值，DATE_BUCKET 就會引發錯誤。
傳回值
了解 DATE_BUCKET 的輸出
DATE_BUCKET 會傳回與 datepart 和 number 參數對應的最新日期或時間值。 例如在下列運算式中，DATE_BUCKET 會傳回 2020-04-13 00:00:00.0000000 的輸出值，因為輸出的計算依據是從預設原始時間 1900-01-01 00:00:00.000 起算為期一週的值區。 值 2020-04-13 00:00:00.0000000 是在原始值 1900-01-01 00:00:00.000 後的 6,276 週。
DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 1, @date);
針對下列所有運算式，系統將會傳回相同的輸出值 2020-04-13 00:00:00.0000000。 這是因為 2020-04-13 00:00:00.0000000 是在原始日期後的 6,276 週，而 6,276 可由 2、3、4 和 6 整除。
DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 2, @date);
SELECT DATE_BUCKET(WEEK, 3, @date);
SELECT DATE_BUCKET(WEEK, 4, @date);
SELECT DATE_BUCKET(WEEK, 6, @date);
下列運算式的輸出是 2020-04-06 00:00:00.0000000，這是預設原始時間 1900-01-01 00:00:00.000 後的 6275 週。
DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 5, @date);
下列運算式的輸出是 2020-06-09 00:00:00.0000000，這是指定原始時間 2019-01-01 00:00:00 後的 75 週。
DECLARE @date DATETIME2 = '2020-06-15 21:22:11';
DECLARE @origin DATETIME2 = '2019-01-01 00:00:00';
SELECT DATE_BUCKET(WEEK, 5, @date, @origin);
datepart 引數
dayofyear、day 和 weekday 都會傳回相同的值。 每個 datepart 及其縮寫都會傳回相同的值。
number 引數
number 引數不得超過正數 int 值的範圍。 在下列陳述式中，number 引數超過 int 的範圍 (超過 1)。 下列陳述式會傳回錯誤訊息 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(DAY, 2147483648, @date);
如果將負數的 number 值傳遞給 DATE_BUCKET 函式，則會傳回下列錯誤。
Msg 9834, Level 16, State 1, Line 1
Invalid bucket width value passed to DATE_BUCKET function. Only positive values are allowed.
date 引數
DATE_BUCKET 會傳回與 date 引數的資料類型對應的基底值。 在下列範例中，系統會傳回具有 datetime2 資料類型的輸出值。
SELECT DATE_BUCKET(DAY, 10, SYSUTCDATETIME());
origin 引數
origin 和 date 引數的資料類型必須相同。 如果使用不同的資料類型，將會產生錯誤。
備註
在下列子句中使用 DATE_BUCKET：
- GROUP BY
- HAVING
- 排序依據
- SELECT <list>
- WHERE
範例
A. 使用值區寬度 1 從原始時間計算 DATE_BUCKET
這些陳述式各自會從原始時間以值區寬度 1 遞增 DATE_BUCKET：
DECLARE @date DATETIME2 = '2020-04-30 21:21:21';
SELECT 'Week', DATE_BUCKET(WEEK, 1, @date)
UNION ALL
SELECT 'Day', DATE_BUCKET(DAY, 1, @date)
UNION ALL
SELECT 'Hour', DATE_BUCKET(HOUR, 1, @date)
UNION ALL
SELECT 'Minutes', DATE_BUCKET(MINUTE, 1, @date)
UNION ALL
SELECT 'Seconds', DATE_BUCKET(SECOND, 1, @date);
結果集如下所示。
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. 使用運算式當做 number 和 date 參數的引數
這些範例會使用不同的運算式類型，作為 number 和 date 參數的引數。
這些範例是使用 AdventureWorksDW2022 資料庫所建置的。
將使用者自訂變數指定為 number 和 date
此範例會將使用者定義變數指定為 number 和 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);
結果集如下所示。
1999-12-08 00:00:00.0000000
將資料行指定為 date
在下列範例中，我們會計算依每週日期值區分組的 OrderQuantity 總和以及 UnitPrice 總和。
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 ShippedDateBucket;
結果集如下所示。
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
將純量系統函數指定為 date
這個範例會針對 date 指定 SYSDATETIME。 傳回的精確值取決於陳述式執行的日期和時間：
SELECT DATE_BUCKET(WEEK, 10, SYSDATETIME());
結果集如下所示。
2020-03-02 00:00:00.0000000
將純量子查詢和純量函數指定為 number 和 date
此範例會使用純量子查詢 MAX(OrderDate)，作為 number 和 date 的引數。 (SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100) 會作為 number 參數的人工引數，以示範如何從值清單中選取 number 引數。
SELECT DATE_BUCKET(WEEK, (
SELECT TOP 1 CustomerKey
FROM dbo.DimCustomer
WHERE GeographyKey > 100
), (
SELECT MAX(OrderDate)
FROM dbo.FactInternetSales
));
將數值運算式和純量系統函數指定為 number 和 date
此範例會使用數值運算式 ((10/2)) 和純量系統函數 (SYSDATETIME) 作為 number 和 date 的引數。
SELECT DATE_BUCKET(WEEK, (10/2), SYSDATETIME());
將彙總視窗函式指定為 number
此範例會使用彙總視窗函式，作為 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. 使用非預設原始值
此範例會使用非預設原始值來產生日期值區。
DECLARE @date DATETIME2 = '2020-06-15 21:22:11';
DECLARE @origin DATETIME2 = '2019-01-01 00:00:00';
SELECT DATE_BUCKET(HOUR, 2, @date, @origin);