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

SqlSql ServerTsqlSql Server-2008## Sql Problem Overview

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 -- nearest hour
```

## Sql Solutions

## Solution 1 - Sql

```
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)
```

will return

```
2007-09-22 15:07:00.000
2007-09-22 15:00:00.000
```

The above just truncates the seconds and minutes, producing the results asked for in the question. As @OMG Ponies pointed 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)
```

and you'll get:

```
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, I would use the above method to truncate the time portion from a datetime to get only the date. The idea is 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 gives you the original date with the time set to `00:00:00.000`

:

```
select dateadd(day, @days, 0)
```

or more succinctly:

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

Using a different datepart (e.g. `hour`

, `mi`

) will work accordingly.

## Solution 2 - Sql

"Rounded" down as in your example. This will return a varchar value of the date.

```
DECLARE @date As DateTime2
SET @date = '2007-09-22 15:07:38.850'
SELECT CONVERT(VARCHAR(16), @date, 120) --2007-09-22 15:07
SELECT CONVERT(VARCHAR(13), @date, 120) --2007-09-22 15
```

## Solution 3 - Sql

I realize this question is ancient and there is an accepted and an alternate answer. I also realize that my answer will only answer half of the question, but for anyone wanting to **round to the nearest minute and still have a datetime compatible value using only a single function**:

```
CAST(YourValueHere as smalldatetime);
```

For hours or seconds, use Jeff Ogata's answer (the accepted answer) above.

## Solution 4 - Sql

`Select convert(char(8), DATEADD(MINUTE, DATEDIFF(MINUTE, 0, getdate), 0), 108) as Time`

will round down seconds to 00