Grouping and Rounding Dates

Ever need to group dates and round them to intervals?  I had this exact need for a report on wait stats I was working on for an upcoming post.  I wanted the flexibility to be able to group a list of dates by either minute, hour, date, etc.  And then also round those dates within those intervals.  For example, round a list of dates to every 15 minutes, every 3 hours, or every 2 days.  At first thought this sounded like something that might be a little tricky to accomplish.  But as I’ll show in the examples below, this is quite easy.

Start With The First of the Month

To resolve this issue, I started by using the logic that I typically when I need to take a date and change it to the first day of a month or quarter.  The logic I use for that utilizes the DATEADD and DATEDIFF functions.  I’ll skip going into how those work since Books Online covers that pretty well.

To find the start of a month or quarter, these functions can be used together to be compare a date and time against the value 0.  Casting 0 as datetime returns the value 1900-01-01 00:00:00.000.  Using the two functions together, use DATEDIFF to determine how many months or quarters have occurred since 1900-01-01.  And then use DATEADD to add those months or quarters to the date 1900-01-01.  Doing this will provide you with the the first day in either the month or the quarter.

The examples below can be used to demonstrate this:

SELECT DATEADD(M, DATEDIFF(M, 0, '20100310'), 0)
GO

SELECT DATEADD(Q, DATEDIFF(Q, 0, '20100310'), 0)
GO

Returning the following results:

Round to the Desired Interval

Now that’s the easy part which leaves the next part, where dates and times need to be rounded not to the 1st of the month put to periods that are determined at execution time.  As I mentioned above, I need to change the logic above such that I can get dates to round to every 1 week or 2 days or 3 hours.

On the surface, this sounded a lot harder than it turned out to be.  Because if you use the % to find the remainder of division, you can easy round any date or time to any logical grouping of dates or times.

Suppose you need to round a datetime to the nearest 4 hours.  Find the distance in hours between 1900-01-01 and the datetime in question.  Take that number and find the modulo using your rounding interval.  In this case that value is 4.  Then subtract the remainder from the distance.  And use the new distance value in the DATEADD function from 1900-01-01.  Hopefully, that didn’t lose too many people.

Rather than explain it another way, take a look at the following T-SQL statements where the datetime is rounded down to the nearest 2, 3, and 4 hour intervals.

--Round down to nearest 2 hours.
SELECT DATEADD(HH, DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')-(DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')%2), 0)
GO

--Round down to nearest 3 hours.
SELECT DATEADD(HH, DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')-(DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')%3), 0)
GO

--Round down to nearest 4 hours.
SELECT DATEADD(HH, DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')-(DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')%4), 0)
GO

These statements produce the following results:

As you’ll hopefully see, this is a very simple technique.  Imagine replacing the hour, day, or month date parts with a variable and then the interval to round the dates with a variable.  Doing that and the expression can be used in a stored procedure or report to group any set of dates along any set of date or time groupings.

Hope you find this as useful as I did.  And in the next couple days, I hope to have something up that uses this technique to make it even easier to use in your own code by providing fully working examples.