SQL Server - Get Datetime with Minute and Seconds Removed (Date and Hour only)

Question

How to get Datetime to retain only its Date and Hour?

The Minutes and Seconds should be converted to 0.

That is, to Convert a DateTime to Date and Hour only.

eg: I want, datetime GetDate() or '2020-01-01 12:45:50' to be, '2020-01-01 12:00:00'

Answer 1

The fastest and most efficient solution to get DateTime without Minutes and Seconds is:

DECLARE @testDate DATETIME = GETDATE();
SELECT DATEADD(hour, DATEDIFF(hour, 0, @testDate), 0);

Below are other alternatives, but slightly less efficient because of the extra CASTs.

Adding the below queries as some might find it more comprehensible.

DECLARE @testDate DATETIME = GETDATE();
SELECT CAST(CAST(@testDate AS DATE) AS DATETIME)+CAST(DATEPART(HOUR,@testDate) AS FLOAT)/24;
SELECT DATEADD(HOUR,DATEPART(HOUR,@testDate),CAST(CAST(@testDate AS DATE) AS DATETIME));