Date Truncation with DATETRUNC() function in SQL Server

Problem

Suppose you are a data strategist or analyst for an organization. You have been tasked with getting actionable insights from customers who want to track customer patterns at different intervals, such as hourly, daily, or weekly. To do this, you need to use several date functions such as DATEADD, DATEDIFF, DATEPART, and DATEFROMPARTS to get the required date format.

In SQL Server 2022, this got a lot easier to do using the DATETRUNC function.

Solution

Date / Time functions are crucial for working with data manipulations for exploratory data analysis. The standard requirements for datetime functions can be:

• Converting different datetime formats to a standard format. For example, one may use formats such as MMDDYYYY/DDMMYYYY/DD-MM-YYYY, etc.
• Calculating the difference between two dates, e.g., calculating a person’s age. It requires the difference between the person’s DOB and the current date.
• Extracting specific parts of the dates. For example, I can extract months and years from the dates for calculation.
• Handling different time zones within global data, such as converting from UTC to IST.
• Truncating dates to get the first day of the month, year, and quarter. For example, we may want to evaluate monthly, quarterly, and yearly sales.

Date Truncation

Let’s consider these date truncation cases.

First Day of Year

Fist, we will truncate to the first day of the year.

To fulfill this requirement, we could use both the DateDiff() and DateAdd() functions, as shown below.

SELECT DATEADD(year, DATEDIFF(year, 0, '2025-02-14 14:55:01.156'), 0) AS [Year];

The output gives the first date of the year, i.e., 1 January of the specified year.

First Day of Month

Similarly, we can use the following query to truncate to month.

The output gives the first date of the month.

SELECT DATEADD(month, DATEDIFF(month, 0, '2025-11-26 10:34:56.147'), 0) AS MonthStart;

First Date of Quarter

The query below truncates the output to the first date of the quarter in which the input date exists.

SELECT DATEADD(quarter, DATEDIFF(quarter, 0, '2025-11-26 10:34:56.147'), 0) 0) AS [Quarter];

SQL DATETRUNC() Function

Starting in SQL Server 2022, there is a new DATETRUNC function to handle such scenarios.

Syntax

The DATETRUNC() function helps truncate the input date per the specified precision. Its syntax is as follows:

DATETRUNC ( datepart , (input)date ) )

Here, the datepart can have one of the following values:

The date parameter can accept any values of smalldatetime, datetime, date, time,datetime2, and datetimeoffset data types.

Example Use of SQL DATETRUNC()

Let’s take the following example. It defines a variable @inputdatevalue of datetime2 datatype and uses the DATETRUNC() function. You can replace the value of <datepart> from the above table by truncating the value as required. For example, to truncate the value as year, use yy, yyyy, or year.

Imagine you are looking at a clock on December 30, 2024, at precisely 15:30:15.1234567.

Now, imagine removing details from this timestamp step by step:

• If we reset the year, we get January 1, 2024.
• If we reset the quarter,, we get October 1, 2024.
• If we reset the month, we get December 1, 2024.
• The week starts on December 29, 2024.
• The day starts at midnight.
• The hour resets to 15:00.
• The minute resets to 15:30.
• The second resets to 15:30:15.
• At the lowest level, we can also remove milliseconds and microseconds.

Using SQL DATETRUNC() with GETDATE() or SYSDATETIME()

You can use the query below. Replace the datepart value to get the required truncated value using the DATEPART function.

DECLARE @inputdatevalue datetime2 = '2024-12-30 15:30:15.1234567';
SELECT DATETRUNC(<datepart>, @inputdatevalue);

The DATEPART function integrates well with getdate() and SYSDATETIME() to get the input datetime value.

Using SQL DATETRUNC() with Queries

To understand the actual use case of the DATETRUNC() function, let’s use the WideWorldImporters sample database and run the query below.

Note: If you do not have the WideWorldImporters database, download it from the Wide World Importers sample databases for Microsoft SQL.

The following query gets sales data from the [Sales].[Orders] table and truncates the dates to the year to get the count of orders in a year.

SELECT 
   DATETRUNC(yy, OrderDate) AS Year,
   COUNT(*) AS YearlySales
FROM [WideWorldImporters].[Sales].[Orders]
GROUP BY DATETRUNC(yy, OrderDate)
ORDER BY 1 desc

Similarly, you get monthly data by changing the parameters in the DATETRUNC() function from yy to mm, as shown below.

SELECT 
   DATETRUNC(mm, OrderDate) AS Month,
   COUNT(*) AS MontlySales
FROM [WideWorldImporters].[Sales].[Orders]
GROUP BY DATETRUNC(mm, OrderDate)
ORDER BY 1 desc

You can simplify using the date comparison with this function. For example, if you are looking for the data for the month of 2016-05-01, you can use the following:

SELECT COUNT(*) AS MontlySales
FROM [WideWorldImporters].[Sales].[Orders]
WHERE DATETRUNC(mm, OrderDate) = '2016-05-01'

The query below finds the total sales for 2016 using the DATETRUNC function with the year clause.

SELECT COUNT(*) AS MontlySales
FROM [WideWorldImporters].[Sales].[Orders]
WHERE DATETRUNC(yy, OrderDate) = '2016'

Performance Impact of DATETRUNC Function

The DATETRUNC function is a simple way to write SQL queries to truncate datetime values to a specific part, such as a year or month. You can use this function to avoid writing complex queries or multiple date/time functions,.

Comparing DATETRUNC to Other Date Functions

I did not find much difference in performance when using this function compared to other functions.

Let’s look at the execution plan for the queries below.

The first query uses the DATETRUNC() function, whereas the second uses DATEADD and DATEDIFF functions for the same output.

USE AdventureWorksLT2022;
GO
SELECT DATETRUNC(year, OrderDate) as [YearofOrder]
FROM SalesLT.SalesOrderHeader
GO
 
SELECT DATEADD(year, DATEDIFF(year, 0, OrderDate), 0)
FROM SalesLT.SalesOrderHeader

The execution plan shows that the costs of both queries are the same and use the same non-clustered index scan to get the output.

Avoid DATETRUNC in SQL WHERE Clause

Developers should try to avoid the function in the WHERE clause to avoid making a non-SARGable query that can be slow or resource intensive. It cannot use the proper indexes even if you have an index on the specified column.

For example, see the DATETRUNC function in the WHERE clause below. SQL Server cannot optimize the query to use the proper indexes to serve these non-sargable queries.

As shown below, it uses a clustered index scan to search for the specific value.

Next Steps

• Explore the date related tips to understand functions and their use cases.
• Learn about SQL Date Data Types – Date, DateTime, DateTime2, Time.
• Explore the SQL Server 2022 T-SQL enhancements.