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)

possible duplicate of [How can I truncate a datetime in SQL Server?](http://stackoverflow.com/questions/923295/how-can-i-truncate-a-datetime-in-sql-server) — Justin Cave, Mar 20 '12 at 08:58
possible duplicate of [T-SQL datetime rounded to nearest minute and nearest hours with using functions](http://stackoverflow.com/questions/6666866/t-sql-datetime-rounded-to-nearest-minute-and-nearest-hours-with-using-functions) — user272735, Jan 13 '15 at 18:42

Mikael Eriksson · Accepted Answer · 2012-03-20T09:30:17.087

59

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)

edited Mar 20 '12 at 09:30

answered Mar 20 '12 at 08:58

Mikael Eriksson

132,594
21
199
273

1

This is so much cleaner than any other solution I've seen, it's worth a vote and a comment. – Chris Jan 20 '13 at 02:44
Yes, you're right. I must've gotten something wrong – Lukas Eder Feb 07 '14 at 21:23
https://docs.microsoft.com/ru-ru/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-2017 – Vadzim Oct 05 '18 at 14:16

score 5 · Answer 2 · edited Mar 20 '12 at 09:01

5

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)

edited Mar 20 '12 at 09:01

marc_s

704,970
168
1,303
1,425

answered Mar 20 '12 at 08:56

SmartestVEGA

7,601
21
79
127

If you post code, XML or data samples, **PLEASE** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Mar 20 '12 at 09:01
thank you for answer .. Mikael Eriksson has a more simple answer. Also + 1 for the answer. Thanks – pufos Mar 20 '12 at 09:29

score 0 · Answer 3 · answered Sep 09 '14 at 12:18

I'm using something like this:

DATEADD(hour,DATEDIFF(hour,'2000-01-01 00:00',[timestamp]),'2000-01-01 00:00')

You can use this formula to truncate to other levels, ie minutes:

DATEADD(minutes,DATEDIFF(minutes,'2000-01-01 00:00',[timestamp]),'2000-01-01 00:00')

You must remember that if you are truncating to seconds level, the maximum difference between the base date and [timestamp] is 68 years.

Truncate date to only hour / minute

3 Answers3

Linked