Round DATETIME to nearest X minute increment

In: SQL Server


15 Aug 2008

Ever needed to round an arbitrary DATETIME to some specified increment (i.e. 5, 10 or 15 minutes)? This function can come in handy for such a task.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
/*****************************************************************************
Purpose: Round datetime to nearest X minute increment
 
Description: This script creates a function to round time to the nearest
	X minute increment. Works with any increment within a day (@min =< 1440)
 
Author: Taylor Gerring
 
Date: 2007-12-17
 
Notes: On very rare occasions the result may produce a small rounding error
	(i.e. 59.997). This appears to be a limitation on how SQL handles.
	conversion. Since the intention is to round anyway, we could certainly
	accomodate this condition, but I've omitted that for the time being.
 
Test Cases:
	select
		  dbo.fn_RoundTimeToMinIncrement('2007-12-17 02:14:49.646', 5) [5]
		, dbo.fn_RoundTimeToMinIncrement('2007-12-17 02:14:49.646', 10) [10]
		, dbo.fn_RoundTimeToMinIncrement('2007-12-17 02:14:49.646', 15) [15]
		, dbo.fn_RoundTimeToMinIncrement('2007-12-17 02:14:49.646', 30) [30]
		, dbo.fn_RoundTimeToMinIncrement('2007-12-17 02:14:49.646', 60) [60]
		, dbo.fn_RoundTimeToMinIncrement('2007-12-17 03:14:49.646', 120) [120]
		, dbo.fn_RoundTimeToMinIncrement('2007-12-17 13:14:49.646', 1440) [1440]
*****************************************************************************/
CREATE FUNCTION dbo.fn_RoundTimeToMinIncrement
(
	  @TIME DATETIME
	, @MIN SMALLINT
)
RETURNS DATETIME
AS
BEGIN
	/*
	 Ensure minutes is a positive number
	*/
	SET @MIN = ABS(@MIN)
 
	IF @MIN > 1440
		SET @MIN = 1440
 
	/*
	 We are going to separate the DATE and TIME parts, do some math and add them together
	*/
	 RETURN
			/*
			 Here's the DATE part
			*/
			  DATEADD(DAY, 0, DATEDIFF(DAY, 0, @TIME))
			/*
			 And now the TIME math
			*/
			+ CAST(ROUND(CAST(CAST(CONVERT(VARCHAR(12), @TIME, 14) AS DATETIME) AS FLOAT) * 1440.0 / @MIN, 0) / (1440.0 / @MIN) AS DATETIME)
END
go
  • Share/Bookmark

Comment Form

  • Kurt Hitchen: A friend has let me "see" there calendar in Google Calendar, but because their calendar is set to pr [...]
  • bugoy8: https://www.google.com/calendar/dav/nba_13_%4cos+%41ngeles+%4cakers#sports@group.v.calendar.google.c [...]
  • Speaker: Wasn't able to understand how to access the shared folder in the quest OS from this tutorial. Hmmm [...]
  • Elena Kuznetsova: I have killed an hour to figure out how to sync Google contacts' birthday and I have this done! U [...]
  • Andy: I have been trying to figure this out forever.....Thank you ever so much!! [...]


This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 United States.