SQL Tutorial : DATETRUNC New T-SQL Enhancements in SQL Server

This post will explain the operation of the recently introduced DATETRUNC function, provide some useful examples, and discuss how it varies from more conventional data manipulation techniques. The purpose of this article is to help SQL developers, DBAs, and data engineers learn how to take advantage of this new feature in SQL Server 2022.

Understanding DATETRUNC

The DATETRUNC function allows you to truncate a datetime or datetime2 value to a specified date part. This means you can strip the time portion or reduce the precision of a date down to a specific unit such as a year, quarter, month, day, hour, minute, or second. The DATETRUNC function is a more streamlined and expressive way to handle date truncation compared to the traditional methods involving DATEADD, DATEDIFF, and CONVERT.

Syntax

DATETRUNC ( datepart, expression )

--datepart: The part of the date to truncate. This can be year, quarter, month, day, hour, minute, second, etc.
--expression: The datetime, smalldatetime, date, or datetime2 expression to truncate.

Examples

Here’s an example of truncating a DateTime value to different date parts using DATETRUNC.

USE [Hostforlife]
GO

DECLARE @date datetime2 = '2024-10-08 13:45:30.123'

-- Truncate to year
SELECT DATETRUNC(year, @date) AS TruncatedToYear

-- Truncate to month
SELECT DATETRUNC(month, @date) AS TruncatedToMonth

-- Truncate to day
SELECT DATETRUNC(day, @date) AS TruncatedToDay

-- Truncate to hour
SELECT DATETRUNC(hour, @date) AS TruncatedToHour

-- Truncate to minute
SELECT DATETRUNC(minute, @date) AS TruncatedToMinute

-- Truncate to second
SELECT DATETRUNC(second, @date) AS TruncatedToSecond

Output

Use Cases

DATETRUNC offers several advantages for common date truncation use cases in SQL Server, making it easier to,

• Group by a Specific Date Part: You can use DATETRUNC to group data by a specific date part (e.g., by month, day, or hour).
• Remove Time from Datetime Values: Often, developers need to work with only the date part, ignoring the time part.
• Aggregate Data at Different Levels: DATETRUNC makes it easy to aggregate data at different levels of granularity such as hourly, daily, weekly, or monthly.
• Date Range Filtering: DATETRUNC helps simplify date-based filtering where you need to match specific parts of a date.

Comparing DATETRUNC with Traditional Methods

Before the introduction of DATETRUNC, SQL Server developers typically used a combination of DATEADD, DATEDIFF, and CONVERT to achieve similar results. Let’s compare DATETRUNC with traditional approaches.

Truncating to Day

The DATETRUNC approach is much simpler and more intuitive compared to the CAST and CONVERT methods. Here’s how you might truncate a datetime value to the day using traditional methods:

USE [Hostforlife]
GO

DECLARE @date datetime2 = '2024-10-08 13:45:30.123';

-- Traditional method
SELECT CAST(CONVERT(date, @date) AS datetime) AS TruncatedToDay;

-- Using DATETRUNC
SELECT DATETRUNC(day, @date) AS TruncatedToDay;

Output

Truncating to Month and Year

USE [Hostforlife]
GO

DECLARE @date datetime2 = '2024-10-08 13:45:30.123';

-- Truncating to Month
-- Traditional method
SELECT DATEADD(month, DATEDIFF(month, 0, @date), 0) AS TruncatedToMonth;

-- Using DATETRUNC
SELECT DATETRUNC(month, @date) AS TruncatedToMonth;

-- Truncating to Year
-- Traditional method
SELECT DATEADD(year, DATEDIFF(year, 0, @date), 0) AS TruncatedToYear;

-- Using DATETRUNC
SELECT DATETRUNC(year, @date) AS TruncatedToYear;