MS SQL DATE_BUCKET

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)