Trunc Date In SQL Server

by

Truncating dates in SQL Server is a common task for developers working with SQL Server. In this comprehensive article, I will walk you through everything you need to know about truncating dates in SQL Server with multiple approaches.

Trunc Date In SQL Server

SQL Server 2022 introduced the DATETRUNC function, which simplifies date truncation operations. This function returns a truncated date of the same data type as the input date. In addition to this function, there are other methods available to achieve the same goal.

Method-1 Using DATETRUNC ()

The most straightforward approach is to use the DATETRUNC () for this purpose.

Examples

We can execute the following query to truncate to the month.

SELECT DATETRUNC(month, '2025-04-18 14:30:25');

Similarly, to truncate to a quarter, you can use the following query.

SELECT DATETRUNC(quarter, '2025-04-18 14:30:25');

Now, to truncate to the year, you can use the following query.

SELECT DATETRUNC(year, '2025-04-18 14:30:25');

You can check out the SQL Server DATETRUNC Function for more information.

If you’re using an older version of SQL Server, you don’t have direct access to the DATETRUNC function. So in that case, you can use the following methods.

Method 2: Using DATEADD and DATEDIFF

Example-1:

You can use the below query to truncate to a month using the DATEADD and DATEDIFF functions.

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

After executing the above query, I got the expected output as shown in the screenshot below.

Trunc Date In SQL Server

Example-2:

You can use the below query to truncate to day.

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0);

After executing the above query, I got the expected output as shown in the screenshot below.

trunc date function in sql server with example

Example-3:

You can use the below query to truncate a year.

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0);

After executing the above query, I got the expected output as shown in the screenshot below.

truncate date in sql server

Method 2: Using CAST or CONVERT

Example-1:

We can truncate to day using CONVERT function using the below query.

SELECT CONVERT(DATE, GETDATE());

After executing the above query, I got the expected output as shown in the screenshot below.

truncate date in sql server management studio

Example-2:

We can truncate to day using CAST function using the below query.

SELECT CAST(GETDATE() AS DATE);

I got the expected output after executing the above query as shown in the screenshot below.

truncate date in sql

Method 3: Using Custom Calculations

We can use the below query to get the first day of current month.

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

After executing the above query, I got the expected output as shown in the screenshot below.

trim date in sql server

Simmilarly, We can use the below query to get the first day of current quarter.

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0);

I got the expected output as shown in the below screenshot.

trunc date in sql server with example

Performance Suggestion

Below are some suggestions.

  1. Indexing: Ensure your date columns are properly indexed, especially if you frequently filter or group by truncated dates
  2. Computed Columns: For frequently used truncation operations, consider adding calculated columns.
-- Add a computed column
ALTER TABLE Transactions
ADD TransactionMonth AS DATETRUNC(month, TransactionDate);
  1. DATETRUNC vs. DATEADD/DATEDIFF: In SQL Server 2022+, DATETRUNC is generally more efficient than the DATEADD/DATEDIFF combination as it’s specifically optimized for this purpose

Conclusion

Date truncation in SQL Server is a fundamental skill for a developer. The new DATETRUNC function significantly simplifies these operations in SQL Server 2022, but for older versions, you can also use the above methods mentioned in this article.

You may also like the following articles below