SQL Server 2022 introduced a new T-SQL element, DATETRUNC, that truncates parts of dates. For example:

SELECT DATETRUNC(year, '2017-06-01'); Truncates everything in that date other than the year, so it returns just 2017-01-01 00:00:

You might ask, “Well, why not just use YEAR()?” That’s a good question – there are times when you need a start or end date for a date range, and this could make it easier than trying to construct a full start & end date yourself.

Easier for you, that is – but not necessarily good for performance. Let’s take the Stack Overflow database, Users table, put in an index on LastAccessDate, and then test a few queries that are logically similar – but perform quite differently.

CREATE INDEX LastAccessDate ON dbo.Users(LastAccessDate);SET STATISTICS IO ON;GOSELECT COUNT(*) FROM dbo.UsersWHERE LastAccessDate >= '2017-01-01' AND LastAccessDate < '2018-01-01';SELECT COUNT(*) FROM dbo.UsersWHERE YEAR(LastAccessDate) = 2017;SELECT COUNT(*) FROM dbo.UsersWHERE DATETRUNC(year, LastAccessDate) = 2017; And check out their actual execution plans:

The first one, passing in a specific start & end date, gets the best plan, runs the most quickly, and does the least logical reads (4,299.) It’s a winner by every possible measure except ease of writing the query. When SQL Server is handed a specific start date, it can seek to that specific part of the index, and read only the rows that matched.

DATETRUNC and YEAR both produce much less efficient plans. They scan the entire index (19,918 pages), reading every single row in the table, and run the function against every row, burning more CPU.

SQL Server’s thought process is, and has always been, “I have no idea what’s the first date that would produce YEAR(2017). There’s just no way I could possibly guess that. I might as well read every date since the dawn of time.”

That’s idiotic, and it’s one of the reasons we tell ya to avoid using functions in the WHERE clause. SQL Server 2022’s DATETRUNC is no different.

So why doesn’t Microsoft fix this?YEAR and DATETRUNC are tools, just like any other tool in the carpenter’s workshop. There are lots of times you might need to manipulate dates:

• When constructing a dynamic SQL string, and you want to build a date – sure, using a function to build the WHERE clause string is fine. Just don’t use the function in the WHERE clause itself.
• When constructing the contents of variables
• When constructing the output of the query – sure, using a function like this in the SELECT is fine, because it doesn’t influence the usage of indexes in the query plan

DATETRUNC in the SELECT isn’t so bad.Let’s use it in the SELECT clause to group users together by their last access date. Say we want a report to show trends over time. Here are two ways to write the same basic idea of a query:

SELECT YEAR(LastAccessDate) AS CreationYear,MONTH(LastAccessDate) AS CreationMonth,SUM(1) AS UsersInvolvedFROM dbo.UsersGROUP BY YEAR(LastAccessDate), MONTH(LastAccessDate)ORDER BY 1, 2;SELECT DATETRUNC(MONTH, LastAccessDate) AS CreationMonth,SUM(1) AS UsersInvolvedFROM dbo.UsersGROUP BY DATETRUNC(MONTH, LastAccessDate)ORDER BY 1; The two queries do show the date in two different ways, but the UsersInvolved count is the same – it’s just different ways of rendering the same data:

When you review their actual execution plans, the first one (YEAR/MONTH) is much more complex, and goes parallel to chew through about 4 seconds of CPU time:

Whereas the new DATETRUNC syntax has a cool benefit: it only produces one value (the date), and the data in the index is already sorted by that column. Because of that, we don’t need an expensive sort in the execution plan. And because of that, we don’t need parallelism, either, and we only chew through about two seconds of CPU time. Nifty!

So should you use DATETRUNC? Like with most functions, the answer is yes in the select, but probably not in the FROM/JOIN/WHERE clauses.