A Walkthrough – DATETRUNC Function in SQL Server

In the ever-evolving world of data management, new tools and functions are continually emerging to make our lives easier. One such tool is the DATETRUNC function, introduced in SQL Server 2022. Let’s take a journey to understand how this function can simplify date handling in your SQL queries.

A Walkthrough - DATETRUNC Function in SQL Server datetrunc-800x459

The DATETRUNC Story

Imagine you are a data analyst working for a retail company. You have a massive database of sales transactions, and you need to generate reports that summarize sales data by different time periods—monthly, quarterly, and annually. Traditionally, this task might involve complex and repetitive date manipulation. Enter DATETRUNC, a function designed to truncate dates to the specified precision level, making this task much simpler.

The Basics of DATETRUNC

The syntax for DATETRUNC is straightforward:

DATETRUNC ( datepart, date )

Here, datepart specifies the precision (like year, month, or day), and date is the date value you want to truncate.

Let’s See DATETRUNC in Action

Monthly Sales Report

You want to summarize sales data by month. With DATETRUNC, you can easily truncate each transaction date to the beginning of its month.

SELECT DATETRUNC(month, '2021-12-08 11:30:15.1234567') AS TruncatedDate;
-- Returns: 2021-12-01 00:00:00.0000000

This query converts all transaction dates in December 2021 to 2021-12-01.

Quarterly Overview

Next, you need a quarterly sales report. Again, DATETRUNC makes it simple.

SELECT DATETRUNC(quarter, '2021-12-08 11:30:15.1234567') AS TruncatedDate;
-- Returns: 2021-10-01 00:00:00.0000000

All dates in the last quarter of 2021 are now 2021-10-01.

Yearly Summary

Finally, for an annual report, truncate dates to the beginning of the year.

SELECT DATETRUNC(year, '2021-12-08 11:30:15.1234567') AS TruncatedDate;
-- Returns: 2021-01-01 00:00:00.0000000

All dates for 2021 are now 2021-01-01.

Practical Examples

Weekly Sales Data

Let’s generate a weekly sales summary to track weekly performance:

USE AdventureWorks2022;
SELECT DATETRUNC(week, OrderDate) AS Week,
SUM(TotalDue) AS WeeklySales
FROM Sales.SalesOrderHeader
GROUP BY DATETRUNC(week, OrderDate);

A Walkthrough - DATETRUNC Function in SQL Server datatrunc1

Daily Sales Breakdown

For detailed daily sales analysis:

USE AdventureWorks2022;
SELECT DATETRUNC(day, OrderDate) AS Day,
SUM(TotalDue) AS DailySales
FROM Sales.SalesOrderHeader
GROUP BY DATETRUNC(day, OrderDate);

A Walkthrough - DATETRUNC Function in SQL Server datatrunc2

Simplified Date Comparisons

Standardizing dates makes comparisons straightforward. For example:

USE AdventureWorks2022;
SELECT *
FROM Sales.SalesOrderHeader
WHERE DATETRUNC(month, OrderDate) = '2012-01-01';

A Walkthrough - DATETRUNC Function in SQL Server datatrunc3

This query retrieves all sales orders from January 2022 by truncating OrderDate to the start of the month.

The DATETRUNC function is a powerful addition to SQL Server 2022, offering a simple way to handle date truncation tasks. Whether you’re summarizing sales data or standardizing dates for comparison, DATETRUNC streamlines your SQL queries and boosts productivity.

You can subscribe to my SQL in Sixty Seconds YouTube Channel for more learning.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL DateTime
Previous Post
#TSQL2sday Roundup: Has AI Helped You with Your SQL Server Job?

Related Posts

1 Comment. Leave new

  • Pinal Dave wrote


    USE AdventureWorks2022;
    SELECT *
    FROM Sales.SalesOrderHeader
    WHERE DATETRUNC(month, OrderDate) = ‘2012-01-01’;

    That will make a very slow, resource intensive, non_SARGable query. Using the correct Closed/Open method is NOT going to hurt your “productivity”. Fixing performance problems induced by non-SARGable queries WILL affect your “productivity”.

    Here’s the Closed/Open Method for the WHERE Clause…

    WHERE OrderDate >= ‘2012-01-01’ AND OrderDate < '2012-02-01'

    Reply

Leave a Reply