How to Trim TIME Part in DATETIME Values? – Interview Question of the Week #200

Question: How do you trim minutes and seconds from a datetime while keeping its hour?

Answer: Count the hour boundaries from a fixed anchor, then add that count back to the same anchor. The result starts at the beginning of the hour.

A reader emailed me this precise request: turn 2013-01-15 11:23:49.930 into 2013-01-15 11:00:00.000. Despite the post’s short title, the reader wanted to keep the hour, not remove the entire time portion.

DECLARE @mydate datetime = '2013-01-15T11:23:49.930';
SELECT DATEADD(hour, DATEDIFF(hour, 0, @mydate), 0) AS date_with_hour_only;

-- SQL Server2022 and later:
SELECT DATETRUNC(hour, @mydate) AS date_with_hour_only;

Both results for this input are 2013-01-15 11:00:00.000. In the original expression, zero converts to the datetime anchor 1900-01-01. DATEDIFF counts the intervening hour boundaries, and DATEADD reconstructs the hour boundary. It truncates rather than rounds to the nearest hour.

SQL Server 2022 introduced DATETRUNC, so the second expression states the intent directly and preserves the input’s date/time type. Keep the first approach when targeting earlier releases; do not run the DATETRUNC statement there. For a date with no time at all, CAST(@mydate AS date) is a different answer.

I am happy this was the 200th interview question. Keep sending the practical questions that make the series useful.

Previous questions: index sort direction, index size, and read-ahead.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.