Should You Use SQL Server 2022’s DATETRUNC?

Last Updated 5 months ago

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

SELECT DATETRUNC(year, '2017-06-01');

1	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;
GO

SELECT COUNT(*) FROM dbo.Users
	WHERE LastAccessDate >= '2017-01-01'
	  AND LastAccessDate < '2018-01-01';

SELECT COUNT(*) FROM dbo.Users
	WHERE YEAR(LastAccessDate) = 2017;

SELECT COUNT(*) FROM dbo.Users
	WHERE DATETRUNC(year, LastAccessDate) = 2017;

CREATE INDEX LastAccessDate ON dbo.Users(LastAccessDate);

SET STATISTICS IO ON;

SELECT COUNT(*) FROM dbo.Users

WHERE LastAccessDate >= '2017-01-01'

AND LastAccessDate < '2018-01-01';

SELECT COUNT(*) FROM dbo.Users

WHERE YEAR(LastAccessDate) = 2017;

SELECT COUNT(*) FROM dbo.Users

WHERE 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 UsersInvolved
	FROM dbo.Users
	GROUP BY YEAR(LastAccessDate), MONTH(LastAccessDate)
	ORDER BY 1, 2;

SELECT DATETRUNC(MONTH, LastAccessDate) AS CreationMonth,
	SUM(1) AS UsersInvolved
	FROM dbo.Users
	GROUP BY DATETRUNC(MONTH, LastAccessDate)
	ORDER BY 1;

SELECT YEAR(LastAccessDate) AS CreationYear,

MONTH(LastAccessDate) AS CreationMonth,

SUM(1) AS UsersInvolved

FROM dbo.Users

GROUP BY YEAR(LastAccessDate), MONTH(LastAccessDate)

ORDER BY 1, 2;

SELECT DATETRUNC(MONTH, LastAccessDate) AS CreationMonth,

SUM(1) AS UsersInvolved

FROM dbo.Users

GROUP 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.

[Video] Office Hours: Great Questions, Part 2

Should You Use SQL Server 2022’s STRING_SPLIT?

10 Comments. Leave new

Jeff Moden
December 20, 2022 5:49 pm

Heh. This is actually a really good post because it reminds people that a function is still a function and it’s still going to make for non-Sargable Criteria.

Thanks for what you do, Brent.

Reply
- Brent Ozar
  December 20, 2022 8:33 pm
  
  You’re welcome sir!
  
  Reply
SQLing4ever
December 20, 2022 6:15 pm

FINALLY! Do you know how many times I’ve had to get date parts just to get this functionality for maintenance tasks. This is a feature I didn’t know I needed.

Reply
Jessica
December 20, 2022 7:57 pm

Hey Brent (because mobile and can’t drop it into ssms right now and I’ll forget before getting back somewhere I can)

How does DATETRUNC compare to DATEADD(MONTH, DATEDIFF(MONTH, ‘1900-01-01’, dateColumn),’1900-01-01’)?

My gut says in the where clause it’s exactly the same (functions in the where clause issues as expected), but how does the select compare?

Basically is there any under the hood optimization that would make it make sense to revisit old code after updating to SQL 2022

Reply
- Brent Ozar
  December 20, 2022 8:33 pm
  
  I’m not near SSMS either. Definitely feel free to test out any assumptions when you get back to a computer – that’s why I use open source databases and publish my code, so y’all can help get your questions.
  
  You know, without you asking me to do everything for you. Wink wink.
  
  Reply
  - Alexander Kirstein
    December 29, 2022 3:15 pm
    
    Not considering the omnipotent desire to change the world of DBAs for the better as motive, could it have been more efficient and just as effective to have not answered her question? I fully concur with your wise stance but wonder why the need to expend so much effort better placed elsewhere, especially for a genius. Wink wink.
    
    Reply
    - Brent Ozar
      December 29, 2022 3:27 pm
      
      Because the work I’ve done for you for free is up above in the blog post. There are limits to what I will do for free. I hope that’s fair, and if it’s not, you can take a long walk off a short pier.
      
      Happy holidays.
      
      Reply
Blaine
December 20, 2022 10:13 pm

In the first set of queries the last one for DATETRUNC you have
WHERE DATETRUNC(year, LastAccessDate) = 2017;
and it should be
WHERE DATETRUNC(year, LastAccessDate) = ‘2017-01-01’;
which is what the query plan shows.

Reply
Morten L.
December 25, 2022 9:00 pm

I use to use CAST(datetime_fieldname AS DATE) to obtain same result. Do you know how it differs from DATETRUNC?

Reply
- Brent Ozar
  December 26, 2022 5:53 pm
  
  Definitely feel free to test out any assumptions you have – that’s why I use open source databases and publish my code, so y’all can answer your own questions.
  
  You know, without you asking me to do everything for you. Wink wink.
  
  Reply