32

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)

abatishchev
  • 98,240
  • 88
  • 296
  • 433
pufos
  • 2,890
  • 8
  • 34
  • 38
  • 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
  • In particular, see BG100's answer in that thread. – Justin Cave Mar 20 '12 at 08:59
  • 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

4 Answers4

63
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)
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
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)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SmartestVEGA
  • 8,415
  • 26
  • 86
  • 139
  • 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
4

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)
Charlieface
  • 52,284
  • 6
  • 19
  • 43
Conor Cunningham MSFT
  • 4,151
  • 1
  • 15
  • 21
1

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.

maniek765
  • 37
  • 6