SQL Interview: 51 Truncating datetime values to the minute

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Developer Level: Medium

Question:

You are creating a view in a SQL Server based data warehouse.

You need to query transactions that include a TransactionDateTime column which uses the DateTime data type. The query is as follows:

SELECT TransactionID, TransactionDateTime, CustomerID
FROM Sales.Transactions;

To minimize the size of the analytic model, you need to truncate the TransactionDateTime column to the minute level i.e., remove any seconds.

What is the best way to do this?

Answer:

While you could do this using a DATETIMEFROMPARTS function like this:

SELECT TransactionID, 
       DATETIMEFROMPARTS
       (
           YEAR(TransactionDateTime), 
           MONTH(TransactionDateTime),
           DAY(TransactionDateTime),
           DATEPART(hour, TransactionDateTime),
           DATEPART(minute, TransactionDateTime),
           0, -- seconds
           0 -- milliseconds
       ) AS TransactionDateTime,
       CustomerID
FROM Sales.Transactions;

In current versions of SQL Server, you can use the DATETRUNC function like this:

SELECT TransactionID,
       DATETRUNC(minute, TransactionDateTime) AS TransactionDateTime,
       CustomerID
FROM Sales.Transactions;

2025-05-28