sql server - T-SQL: Round to nearest 15 minute interval

Question

What's the best way to round an HH:MM value to the closest 15 minute interval? I don't track seconds so they don't matter.

00:08:00 becomes 00:15:00 
00:07:00 becomes 00:00:00 
01:59:00 becomes 02:00:00 

and so on. Is there an elegant, non UDF or Case statement method for doing this?

EDIT: Here's the SQL I'm using to get the above values that I'd like to round:

CONVERT(CHAR(8), DATEADD(n, SUM(DATEDIFF(n, starttime, stoptime)), 0), 108)

starttime and stoptime are SQL datetimes.

Answer 1

I am currently using a dateadd / datediff variant with a zero (0) date for this. No Casting required:

select dateadd(minute, datediff(minute,0,GETDATE()) / 15 * 15, 0)

GETDATE() is whatever your datetime is.

This will work for dates at least up to the year 5500 before the datediff failes because of an overflow. However if you try to use second accuracy, above will fail right away.

Using another fixed date, like '2009-01-01', or Today's date (warning, more ugly SQL) will fix that. A future date will also work. As long as it has a time part of 00:00:00 you can base another datetime on it.

for example: round to the nearest 30 seconds:

select dateadd(second, round(datediff(second, '2010-01-01', GETDATE()) / 30.0, 0) * 30, '2010-01-01');