T-SQL datetime rounded to nearest minute and nearest hours with using functions

Question

In SQL Server 2008, I would like to get datetime column rounded to nearest hour and nearest minute preferably with existing functions in 2008.

For this column value 2007-09-22 15:07:38.850, the output will look like:

2007-09-22 15:08 -- nearest minute

2007-09-22 15    -- the nearest hour

Answer 1

Query:

declare @dt datetime

set @dt = '09-22-2007 15:07:38.850'

select dateadd(mi, datediff(mi, 0, @dt), 0)

select dateadd(hour, datediff(hour, 0, @dt), 0)

The above code will return this:

2007-09-22 15:07:00.000

2007-09-22 15:00:00.000

This will just truncate the seconds and minutes, producing the results asked for in the question. As @OMG point out, if you want to round up/down, then you can add half a minute or half an hour respectively, then truncate:

select dateadd(mi, datediff(mi, 0, dateadd(s, 30, @dt)), 0)

select dateadd(hour, datediff(hour, 0, dateadd(mi, 30, @dt)), 0)

Output:

2007-09-22 15:08:00.000

2007-09-22 15:00:00.000

Before the date data type was added in SQL Server 2008, you can use the above method to truncate the time portion from a datetime to get only the date. To determine the number of days between the datetime in question and a fixed point in time (0, which implicitly casts to 1900-01-01 00:00:00.000):

declare @days int

set @days = datediff(day, 0, @dt)

And then add that number of days to the fixed point in time, which will give you the original date with the time set to 00:00:00.000:

select dateadd(day, @days, 0)

In brief, you can use:

select dateadd(day, datediff(day, 0, @dt), 0)

 Try using a different datepart (e.g. hour, mi)  this will work.