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

Question

I would like to get datetime column rounded to nearest hour and nearest minute preferably with existing functions.

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

Jeff Ogata · Accepted Answer · 2011-07-12T16:20:44.160

248

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.

edited Jul 12 '11 at 16:20

answered Jul 12 '11 at 15:43

Jeff Ogata

56,645
19
114
127

3

I doubt anyone else will run into this, but if you are trying to round UP to the nearest second and adding on 500 milliseconds you'll want to do datediff(second, '1/1/2000', .... vs datediff(second, 0 .... as you'll get an overflow error. seconds from 0 is too big I guess. – Eric Twilegar Feb 24 '16 at 16:54
'Add the number of hours since Jan 1st 1900, to Jan 1st 1900' — I encountered this in Java/SQL, where it looks ugly. In my case it really should have been done on the Java side. – Corwin Newall Aug 03 '19 at 20:24
For computations with `datetimeoffset`, I had to substitute `0` with `TODATETIMEOFFSET('1900-01-01 00:00:00', 0)` to avoid forcing the local time zone onto the result. – krlmlr Dec 17 '19 at 13:11
'10-23-2020 14:59:59.999' gives 2020-10-23 15:00:00.000 while '10-23-2020 14:59:59.998' 2020-10-23 14:00:00.000 – mit Oct 23 '20 at 12:02
@mit - `DATETIME` has precision "only" 3.33 μs, so '14:59:59.999' gets stored as '15:00:00.000' and '14:59:59.998' as '14:59:59.997' – andowero Mar 18 '21 at 06:38
The round method does not work when you have 28 seconds so that means also when you have 58 seconds if you changed it to subtract 30 seconds – Dan Feb 15 '22 at 06:49

score 28 · Answer 2 · answered Jul 12 '11 at 15:53

28

"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

answered Jul 12 '11 at 15:53

Magnus

45,362
8
80
118

Convert the varchar back to datetime could do (for hour): `CONVERT(datetime, CONVERT(VARCHAR(13), @date, 120)+':00:00')` – Decula Oct 24 '18 at 18:07

score 26 · Answer 3 · answered Nov 16 '18 at 22:19

26

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.

answered Nov 16 '18 at 22:19

Andrew Steitz

1,856
15
29

2

Excellent answer, I've verified that it does round and not just truncate. For anyone else looking into this option, `smalldatetime` was added in SQL 2008. – BradC Apr 12 '19 at 16:44
This is the best option if you're in need of fixing some data in your table that only requires time out to the minute. – Encryption Mar 25 '20 at 13:50

score 2 · Answer 4 · edited May 22 '23 at 16:52

In the new SQL Server 2022, and in Azure SQL, you can use the DATETRUNC function:

SELECT DATETRUNC(minute, @yourDate)
SELECT DATETRUNC(hour, @yourDate)

You can also use the DATE_BUCKET function if you want to round to an arbitrary number of hours or minutes

SELECT DATE_BUCKET(minute, 3, @yourDate)
SELECT DATE_BUCKET(hour, 3, @yourDate)

You can even give it a fourth parameter with a starting point, see the documentation.

score 0 · Answer 5 · edited Jan 13 '20 at 15:01

0

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

will round down seconds to 00

edited Jan 13 '20 at 15:01

Arun Vinoth-Precog Tech - MVP

22,364
14
59
168

answered Jan 13 '20 at 14:44

Jam_Jam

47
1
6

Very slow, and does not round off as the OP asked, but rounds down, for which he did not ask. (Disclaimer - I did not down-vote you) – Reversed Engineer Mar 18 '20 at 09:32

score 0 · Answer 6 · answered May 18 '23 at 20:57

Though there is an accepted answer for so long here I would like to point out that OP's desired output was

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

So, for nearest minute I have just added 30 seconds and collected the value up to minute by converting it into varchar. And for nearest minute I have added 30 minutes and collected the value up to hour part. Voila!!!

Query:

declare @dt datetime

set @dt = '09-22-2007 15:07:38.850'
select convert(varchar(16),dateadd(SECOND,30,@dt),120) nearest_minute

Output:

nearest_minute
2007-09-22 15:08

Query:

declare @dt datetime
set @dt = '09-22-2007 15:07:28.850'
select convert(varchar(16),dateadd(SECOND,30,@dt),120) nearest_minute

Output:

nearest_minute
2007-09-22 15:07

Query:

declare @dt datetime
set @dt = '09-22-2007 15:07:38.850'
select convert(varchar(13),dateadd(MINUTE,30,@dt),120) nearest_hour

Output:

nearest_hour
2007-09-22 15

Query:

declare @dt datetime
set @dt = '09-22-2007 15:30:28.850'
select convert(varchar(13),dateadd(MINUTE,30,@dt),120) nearest_hour

Output:

nearest_hour
2007-09-22 16

fiddle

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

6 Answers6

Linked

Related