How to use DATE_BUCKET() Function in SQL Server

     

      The Date_Bucket function is introduced in Azure SQL Edge which is mainly used by IoT devices. This useful function returns the date-time value corresponding to the start of each date-time bucket from the timestamp defined by the origin parameter, or the default origin value of 1900-01-01 00:00:00.000. In other words, it lets you arrange data into groups that represent fixed intervals of time. SQL Server 2022 includes this useful function in its database engine.

Results of Data_Bucket might be confusing, Let's look at its syntax first.

DATE_BUCKET (datepart, number, date, origin)

datepart

Datepart needs to be one of the following datepart list, this parameter controls the size of 1 bucket. Do you want to get the summary of invoices in a week, a month, or a minute? datepart will control the default size of a bucket.

datepart	Abbreviations
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

You can change the bucket size with the number parameter. For example, if you pick week for the datepart, you can easily change the bucket size to 2 weeks by passing 2 to this parameter.

date

The date parameter is the source of the DateTime column in your table.

origin

origin is optional, you can force SQL Server to start from a custom date if you like. If your company's operation week different, you can control the calculation by passing its origin.

     Now, enough talk. Let's see what this function can accomplish for you! I am using the WideWorldImporters Database for the following examples. I want to see the number of customers for each week from my Invoice table. I passed week for the first parameter, and 1 for the number because I want buckets to be exactly 1 week. InvoiceDate is the name of the column from my table.

  SELECT DATE_BUCKET(week,1, InvoiceDate) as InvoiceWeek, 
    COUNT(CustomerId) as CustomerCount
  FROM Sales.Invoices
  GROUP BY DATE_BUCKET(week,1, InvoiceDate)
  ORDER BY InvoiceWeek    

     As you can see CustomerCount gives me the number of customers for each week. InvoiceWeek gets increased by 7 days. If you like to see the numbers bi-weekly, then all you need to do is to change the number parameter to 2.

  SELECT DATE_BUCKET(week,2, InvoiceDate) as InvoiceWeek, 
    COUNT(CustomerId) as CustomerCount
  FROM Sales.Invoices
  GROUP BY DATE_BUCKET(week,2, InvoiceDate)
  ORDER BY InvoiceWeek    

   The CustomerCount is larger for each bucket and each bucket is getting increased by two weeks rather than one because I passed 2 to the number parameter.

You can find DATE_BUCKET's documentation here.