How to use SQL Server DATE_BUCKET Function

This SQL Server tutorial will teach you about the SQL Server DATE_BUCKET function with syntax and examples.

You will understand how to analyse time-based data using the DATE_BUCKET() function, where you will apply the DATE_BUCKET() function on the table and where it can be beneficial.

Table of Contents

SQL Server DATE_BUCKET Function

SQL Server DATE_BUCKET function creates the data bucket based on the date and time values. Simply put, it groups the data on the data and time value or aggregates it over specific time intervals such as week, month, year, etc.

The syntax is given below.

DATE_BUCKET(datepart, number, date, origin)

Where,

DATE_BUCKET(): This is the function for bucketing the data on specified data and time values.

datepart: It is the part of the date to compute the bucket for the day, week, month, year, etc.
number: It is used to specify the size (width) of each bucket, such as one week, one year, etc. This parameter is always used with the datepart parameter.
date: It is the data time value or the column based on the bucketing is done. The date can be of these data types: date, datetime, datetime2, datetimeoffset, smalldatetime, and time.
origin: It is used to specify the starting date and time of the bucket; if not specified, then SQL Server uses the default date and time value.

The data type of the date and origin parameter of the DATE_BUCKET() should be the same.

You can’t use the user-defined variable as the datepart argument to the DATE_BUCKET() function, but use the following datepart:

date_part	Meaning of datepart
yy, yyyy, year	This means the year part of the given date.
qq, q, quarter	It represents the quarter part of the given date.
mm, m, month	This is the month part of the date.
dd, d, day	day part of the date.
wk, ww, week	This is the week part of the given date.
mi,n, minute	It is the minute part.
hh, hour	The hour part.
ss,s, second	This is the second part.
ms, millisecond	This is the milliseconds

You can use the DATE_BUCKET() function in the clauses such as GROUP BY, HAVING, SELECT <list>, ORDER BY, and WHERE.

For example, you have a UserLogins table that records each time a user logs into a system with columns UserID and LoginTime, as shown below.

SQL Server DATE_BUCKET Function UserLogins Table

Suppose you need to find how many logins occur each month. Using the query below, you can use the SQL Server DATE_BUCKET function to group the user login time into monthly buckets.

SELECT
	DATE_BUCKET(MONTH, 1, LoginTime) AS Month,
	COUNT(*) AS TotalUserLogins
FROM
	UserLogins
GROUP BY
	DATE_BUCKET(MONTH, 1, LoginTime);

SQL Server DATE_BUCKET Function Applying on Table

After executing the above query, it returns the result set containing the total logins for each month. As you can see, a 3-time login occurs each month from the output.

Let’s understand the query part, DATE_BUCKET(MONTH, 1, LoginTime). This creates a bucket of 1 month each based on the values in the LoginTime column. Here, the datepart is MONTH, the number is 1, and the date is the LoginTime column value.

and the part COUNT(*), which counts the logins in each bucket. After that, GROUP BY DATE_BUCKET(MONTH, 1, LoginTime). This part of the GROUP BY clause groups the rows by the monthly buckets.

So, the result set contains a list of months with the total number of user logins for each month. For example, for the month 2023-01-01 00:00:00.000, number of logins is 3.

Again, find the number of logins each week using the below query.

SELECT
	DATE_BUCKET(WEEK, 1, LoginTime) AS Week,
	COUNT(*) AS TotalUserLogins
FROM
	UserLogins
GROUP BY
	DATE_BUCKET(WEEK, 1, LoginTime);

SQL Server DATE_BUCKET Function Applying on Table For Week

After executing the above query, it returns the result set containing the total logins for each week. As you can see, a varying login occurs each week from the output. Here, the datepart is WEEK, the number is 1, and the date is the LoginTime column.

This is how to use the SQL Server DATE_BUCKET function to create the bucket based on the data and time values.

You can use the DATE_BUCKET() function for time series analysis, such as tracking the changes over time, for example, stock market data, user activity logs or weather records. As you know, this function can group the data into meaningful time intervals (daily, monthly, etc) for trend analysis.
Businesses can use the DATE_BUCKET() function to analyze the sales data to know the daily, weekly, or monthly trends, patterns, and seasonality by creating the bucket.
As you in financial reporting, reports are mostly generated monthly, quarterly, or yearly. Here you can use the DATE_BUCKET() function to group financial transactions into specific periods.

From the above three points, now you know how the SQL Server DATE_BUCKET function can be helpful.

Conclusion

In this SQL Server tutorial, you learned how to analyse the date and time by creating the bucket based on the date and time value using the SQL Server DATE_BUCKET function.