How can I truncate a datetime in SQL Server?

Seizing precision

Let’s dig into different scenarios and their solutions to fine-tune your datetime truncation skills in SQL Server.

Keeping the day, throwing out the time

To discard the time and keep only the date part:

SELECT CAST(<your_datetime_column> AS date) 
FROM your_table;


Truncating till hour or minute

When you have to keep the date same and round off the time to the nearest hour or minute:

-- For hour
SELECT DATEADD(hour, DATEDIFF(hour, 0, <your_datetime_column>), 0) 
FROM your_table;

-- For minute
SELECT DATEADD(minute, DATEDIFF(minute, 0, <your_datetime_column>), 0) 
FROM your_table;


Here we utilize DATEADD and DATEDIFF for accurate truncation.

Navigating the edge cases

As we say, not all timestamps wear capes. Let’s solve some unusual datetime issues in SQL Server:

Preventing overflow

-- No overflow today, thank you!
SELECT DATEADD(second, DATEDIFF(second, '20000101', <your_datetime_column>), '20000101') 
FROM your_table;


Choose a date close to your data to prevent overflow.

Keeping the datetime format

Retain datetime format while truncating time:

-- Just a little touch-up to keep in style!
SELECT CAST(CAST(<your_datetime_column> AS date) AS datetime) 
FROM your_table;


This gives you 00:00:00.000 as the time.

Truncation strategy

Evade the string menace

Remember, converting datetimes to strings for truncation might be alluring, but it's the dark side of coding - it impacts your performance and accuracy.

Believing in your app

Let your application take the wheel sometimes. If it can handle truncation smartly, lighten the load on your database.

Leverage built-in functions

SQL Server has in-built functions that can be valuable allies in truncating datetime.

System-friendly datetime

-- The system knows best!
SELECT CONVERT(DATE, CURRENT_TIMESTAMP);


This operation is always based on the server's current date and time.

Microsoft to the rescue

Microsoft's documentation has more insights on date parts that can be used with various date functions.

Query like a pro

Love thy indexes

datetime columns that are indexed can speed up the performance.

Filter then truncate

If possible, apply filters before truncating datetime. It makes your query more efficient.