Simpler Date Patterns in SQL Server

Date handling in SQL Server tends to accumulate tried and trusted combinations of DATEPART(), DATEADD(), and DATEDIFF() – with nested variations. The challenge with these isn’t raw performance, but more with conveying intent and readability.

So let’s look at some simpler patterns to try and avoid some of these and be clearer with what we’re trying to achieve.

Natural boundaries

A frequent use case – particularly when it comes to analytics – is to identify the start of a natural boundary such as week or month. Finding the start of the current month may be like this:

/* Using DATEFROMPARTS */
SELECT MonthStart = DATEFROMPARTS(
	YEAR(GETDATE()),
	MONTH(GETDATE()),
	1);

/* Using DATEADD */
SELECT MonthStart = CAST(
	DATEADD(
		DAY,
		-(DAY(GETDATE()) - 1),
		GETDATE())
	AS DATE);

The first is slightly nicer, but neither is particularly elegant.

From SQL Server 2022 onwards (plus Azure and Fabric) we have the DATETRUNC function which truncates a date based on a given date-part – such as month, week, etc. This allows us to replace the above with:

SELECT MonthStart = DATETRUNC(
	MONTH,
	GETDATE());

Much tidier, much clearer. Less mental-cpu-cycles required.

It’s worth noting that when using the date-part as WEEK, the first day of the week defaults based on your environment language. It’s the same as querying DATEPART(WEEKDAY, ...). Query @@DATEFIRST to check, or run SET DATEFIRST first to override the default.

Fun fact 🎉 DATETRUNC will return the same data type you provide to it.

Custom intervals

Sometimes natural boundaries aren’t enough and we need custom time ranges – for example 4-week reporting periods, or aggregating to 15-minute windows.

In this instance we might find convoluted logic like exploiting integer division below:

SELECT Nearest15 = DATEADD(
    MINUTE,
    DATEDIFF(
		MINUTE,
		0,
		GETDATE()
		) / 15 * 15,
    0
);

Thankfully SQL Server 2022 came to the rescue again with the DATE_BUCKET function. Its syntax is similar to the DATETRUNC function as we provide a datepart and date parameter, but here we also choose the ‘bucket’ size. Here’s our 15-minute example above, but now much clearer:

SELECT Nearest15 = DATE_BUCKET(
	MINUTE,
	15,
	GETDATE());

A word of caution here is that buckets are calculated from a start point of 1900-01-01 00:00:00.000 so most dateparts work as expected starting from a zero / default value. However, 1st Jan 1900 was a Monday so when using a WEEK it’ll be relative to the Monday instead of your DATEFIRST setting we looked at above.

To resolve this, there’s an optional 4th parameter called origin to set the start point from which the boundaries will be created. For example you might want to set a Sunday for weekly buckets, or a particular time if you want to offset those.

/* Minute bucketed to 05/20/35/50 */
SELECT Bucket = DATE_BUCKET(
	MINUTE,
	15,
	GETDATE(),
	CAST('1900-01-01 00:05:00' AS DATETIME)
	);

/* 4 weeks starting Sunday */
SELECT WeekBucket = DATE_BUCKET(
	WEEK,
	4,
	GETDATE(),
	CAST('2026-01-04' AS DATETIME)
);

This provides similar functionality to the DATETRUNC function but with much more flexibility. For most fixed interval requirements, this provides a cleaner solution than the alternatives.

Word of warning ⚠️ if using the origin parameter, it must be of the same type as the date provided.

Rebuilding dates

Whilst we’ve covered 2 newer functions to help us out, this isn’t about replacing older functions with newer ones, it’s about using the right choice for clarity in delivering the result. For that reason, these functions won’t always supersede the trusty DATEFROMPARTS function.

The first example in the post showed how we replace DATEFROMPARTS with a more concise function. Where this function shines is when we’re rebuilding dates from individual values. For example a proc or function which receives a @Year and@Month parameter would make great use of this:

SELECT DATEFROMPARTS(@Year, @Month, 1);

The function can become bloated where the definition is complicated by simultaneously deconstructing and then reconstructing a date. In those instances, the previous approaches may be more appropriate.

When we’ve already got the parts and need to re-assemble them, this is the perfect solution.

End of month

Our final and very specific example is for calculating the end of a month. It’s the opposite end of the spectrum to the DATETRUNC we started with. This is an older function but one that still shines in organisations with monthly reporting cadence.

Getting the end of the month can be janky, for example:

SELECT MonthEnd = CAST(
	DATEADD(
		DAY,
		-DATEPART(
			DAY,
			GETDATE()),
		DATEADD(
			MONTH,
			1,
			GETDATE())
		)
	AS DATE);

Again we can demonstrate a much cleaner example to write:

SELECT MonthEnd = EOMONTH(
	GETDATE());

As well as being clearer, it also supports offsets, for example:

/* End of last month */
SELECT MonthEnd = EOMONTH(
	GETDATE(),
	-1);

/* End of next month */
SELECT MonthEnd = EOMONTH(
	GETDATE(),
	1);

However, the drawback is we only have this for months. There is no week or year equivalent, so it may make code more confusing if you’re mixing and matching approaches for determining the end of a week versus a month.

Wrap up

In this post we’ve looked at some lesser used functions which can deliver simpler and cleaner patterns when handling dates. This isn’t about focussing on shiny new functions, but rather the right choice to deliver the correct result – in a way that is clearly communicated by the code.

In summary, we have:

• DATETRUNC to find a natural boundary
• DATE_BUCKET when we want customisable boundaries
• DATEADD is still used for dates relative to your boundaries
• DATEFROMPARTS to construct from individual pieces
• EOMONTH explicitly for month end and relative months

One footnote to add is that DATETRUNC and DATE_BUCKET functions are nondeterministic so if you’re looking to include these in a persisted computed column expression for example, you’d want to avoid them.

Consistency is also key to clarity, so whether these functions or others are right for your solutions, use them consistently.

Even when writing these examples I managed to trip myself up with DATEADD and an edge case issue. If you’ve fallen into similar traps before, maybe it’s worth considering some of these functions to demonstrate the intent in a clearer way.