Truncate date to only hour / minute

Question

How do i trunc the date in sql server 2008 like this :

I have 2012-01-02 12:04:11.443 and I want only to select 2012-01-02 12:00:00.000 and 2012-01-02 12:04:00.000 (hour and minute level)

Answer 1

declare @D as datetime = '2012-01-02T12:04:11.443'

select dateadd(hour, datediff(hour, 0, @D), 0)
select dateadd(minute, datediff(minute, 0, @D), 0)

Answer 2

This seems to work:

select [Rounded Time] =
    dateadd(mi,datediff(mi,0,dateadd(ss,30,a.DT)),0)

from
    (
    select dt = convert(datetime,'8:24:29.997')
    union all
    select dt = convert(datetime,'8:24:30.000')
    ) a

Results:

Rounded Time                                           
------------------------------------------------------ 
1900-01-01 08:24:00.000
1900-01-01 08:25:00.000

(2 row(s) affected)

Answer 3

As of SQL Server 2022 CTP 2.1, there is a native function in SQL to do this called DATETRUNC(). You can pick the date/time level to which you would like to truncate the date. Documentation is here.

DATETRUNC(hour, YourDate)