0

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'

Habeeb
  • 7,601
  • 1
  • 30
  • 33
  • @mitch-wheat - please check double check marking of duplicate. It is not a duplicate question and please mark it as not duplicate. – Habeeb Apr 19 '20 at 08:21
  • 1
    This question asks about removing just the minutes/seconds portion of the datetime. It's not really a duplicate of https://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server as that question is about removing the entire time portion from the date. Perhaps there is a different question this is a duplicate of, but the one referenced is not it. Voting to re-open and this can be shut down again if a proper duplicate is found and referenced. – jeremysawesome May 05 '23 at 22:53

1 Answers1

0

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));
Habeeb
  • 7,601
  • 1
  • 30
  • 33