
In this post, we will explore the DATE_BUCKET function, a powerful tool introduced in Microsoft SQL Server 2022.
This function is used to truncate dates to a specified granularity.
We will discuss its syntax, see how it was handled before, and compare its performance to previous methods.
Syntax:
DATE_BUCKET (datepart, number, date [, origin ] )
- Datepart - The part of date that is used with the number parameter, for example, year, month, day, minute, second
- Number - The integer number that decides the width of the bucket combined with datepart argument. This represents the width of the datepart buckets from the origin time. This argument can't be a negative integer value
- Date - An expression that can resolve to one of the following values: date, datetime, datetime2, datetimeoffset, smalldatetime, time
- Origin - An optional expression that match the data type of the date parameter. DATE_BUCKET uses a default origin date value of 1900-01-01 00:00:00.000 that is, 12:00 AM on Monday, January 1, 1900, if no origin value is specified for the function.
DATE_BUCKET returns the latest date or time value, corresponding to the datepart and number parameter.
Let's now see the new function in action and let's compare the code with some code used to accomplish the task before.
Examples
Example 1: Let's find the first day (date) of the month for some date time values
-- Old way
SELECT
name
, modify_date
, MonthModified = = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', modify_date), '19000101')
FROM sys.all_objects;
-- New function
SELECT
name
, modify_date
, MonthModified = DATE_BUCKET(MONTH, 1, modify_date)
FROM sys.all_objects;

Example 2: Number of purchases per month
SELECT
Month = DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1)
, NumberOfPurchases = COUNT(*)
FROM
Sales.SalesOrderHeader
GROUP BY
DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1);
SELECT
Month = DATE_BUCKET(MONTH, 1, OrderDate)
, NumberOfPurchases = COUNT(*)
FROM
Sales.SalesOrderHeader
GROUP BY
DATE_BUCKET(MONTH, 1, OrderDate);

Example 3: Number of deliveries per hour
SELECT
Interval = DATE_BUCKET(HOUR, 1, [ConfirmedDeliveryTime])
, Number = COUNT(*)
FROM
[Sales].[Invoices]
GROUP BY
DATE_BUCKET(HOUR, 1, [ConfirmedDeliveryTime]);

We can conclude that the new function makes working with the time data type much easier.
*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (1.16 kb)