SQL Server Truncate Date to Minute

In this comprehensive article, I’ll walk you through multiple methods to truncate dates to the minute in SQL Server, explain when to use each approach.

SQL Server Truncate Date to Minute

Before diving into the methods, let me explain why truncating datetime values to the minute is so important.

Common scenarios where minute-level truncation is essential include:

• Performance monitoring: Grouping system events by minute intervals
• Financial reporting: Aggregating trading data at minute-level precision
• Healthcare analytics: Tracking patient vital signs at specific time intervals
• Web analytics: Analyzing user activity patterns in minute-based buckets
• Manufacturing data: Monitoring equipment performance at regular intervals

Understanding SQL Server Date Truncation Concepts

When working with datetime truncation in SQL Server, it’s essential to understand that we’re essentially removing the seconds and milliseconds components while preserving the year, month, day, hour, and minute components.

For example:

• Original: 2025-01-15 14:32:47.523
• Truncated to minutes: 2025-01-15 14:32:00.000

Method 1: Using DATEADD and DATEDIFF Functions (Classic Approach)

The traditional and most widely compatible method I’ve used across different SQL Server versions involves combining DATEADD and DATEDIFF functions.

Syntax

SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, your_datetime_column), 0) AS TruncatedToMinute
FROM your_table;

Example

-- Create sample data
DECLARE @SampleDateTime DATETIME = '2025-08-10 14:32:47.523';

-- Truncate to minute
SELECT 
    @SampleDateTime AS OriginalDateTime,
    DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @SampleDateTime), 0) AS TruncatedToMinute;

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

How It Works

• DATEDIFF(MINUTE, 0, your_datetime) calculates the number of minutes since the base date (1900-01-01)
• DATEADD(MINUTE, calculated_minutes, 0) adds those minutes back to the base date, effectively removing seconds and milliseconds

Real-World Application

We used it to analyze customer checkout patterns using the following query.

SELECT 
    DATEADD(MINUTE, DATEDIFF(MINUTE, 0, EndTime), 0) AS checkout_minute,
    COUNT(*) AS transactions_per_minute
FROM ShiftSchedule
WHERE EndTime >= '2024-01-01'
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, EndTime), 0)
ORDER BY checkout_minute;

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

Method 2: Using DATETRUNC Function (SQL Server 2022+)

SQL Server 2022 introduced the DATETRUNC Function, which provides a more intuitive and readable approach to date truncation.

Syntax

SELECT DATETRUNC(MINUTE, your_datetime_column) AS TruncatedToMinute
FROM your_table;

Example

-- Using DATETRUNC function (SQL Server 2022+)
DECLARE @SampleDateTime DATETIME = '2025-08-10 14:32:47.523';

SELECT 
    @SampleDateTime AS OriginalDateTime,
    DATETRUNC(MINUTE, @SampleDateTime) AS TruncatedToMinute;

Advantages of DATETRUNC

• Simplicity: More readable and intuitive syntax
• Performance: Optimized for modern SQL Server versions
• Flexibility: Supports multiple dateparts in a single function
• Maintainability: Easier for team members to understand and modify

As mentioned in the search results, DATETRUNC is a powerful addition to SQL Server 2022, offering a simple way to handle date truncation tasks.

Method 3: Manual Truncation Using DATEADD

Another approach I’ve used, particularly when working with applications that require explicit control over the truncation process, involves manually setting seconds and milliseconds to zero.

Syntax

SELECT DATEADD(SECOND, -DATEPART(SECOND, your_datetime_column), 
       DATEADD(MILLISECOND, -DATEPART(MILLISECOND, your_datetime_column), your_datetime_column))
FROM your_table;

Example

DECLARE @SampleDateTime DATETIME = '2025-08-10 14:32:47.523';

SELECT 
    @SampleDateTime AS OriginalDateTime,
    DATEADD(SECOND, -DATEPART(SECOND, @SampleDateTime), 
    DATEADD(MILLISECOND, -DATEPART(MILLISECOND, @SampleDateTime), @SampleDateTime)) AS TruncatedToMinute;

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

Method 4: Using FORMAT and CAST Functions

For scenarios where you need string formatting flexibility, this method combines FORMAT and CAST functions.

Syntax

SELECT CAST(FORMAT(your_datetime_column, 'yyyy-MM-dd HH:mm:00.000') AS DATETIME) AS TruncatedToMinute
FROM your_table;

Example

DECLARE @SampleDateTime DATETIME = '2025-08-10 14:32:47.523';

SELECT 
    @SampleDateTime AS OriginalDateTime,
    CAST(FORMAT(@SampleDateTime, 'yyyy-MM-dd HH:mm:00.000') AS DATETIME) AS TruncatedToMinute;

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

Performance Comparison of Different Methods

Here’s a performance comparison of the different methods:

Best Practices for Date Truncation

Below are the best practices:

1. Choose the Right Method for Your SQL Server Version

• SQL Server 2022+: Use DATETRUNC for new development
• Older versions: Stick with DATEADD/DATEDIFF Combination
• Mixed environments: Use DATEADD/DATEDIFF for consistency

2. Consider Index Usage

When truncating dates for filtering or grouping, ensure your indexes support the operations:

-- Create computed column for better performance
ALTER TABLE ShiftSchedule
ADD truncated_timestamp AS DATEADD(MINUTE, DATEDIFF(MINUTE, 0, EndTime), 0);

-- Create index on computed column
CREATE INDEX IX_TruncatedTimestamp ON ShiftSchedule (truncated_timestamp);

Check out the screenshot below for your reference.

Conclusion:

Truncating datetime values to the minute level is essential for any SQL Server professional.

Here’s the final recommendation

Remember always to test your chosen method with your specific dataset and use case.

You may also like the following articles

• Truncate Table In SQL Server
• SQL Server Date Minus 1 Month
• SQL Server Concat Date and Time into DateTime
• Get Financial Year from Current Date in SQL Server
• SQL Server Get The Latest Record By Date
• SQL Server Date Difference in Hours

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.