A colleague asked me if I had any TSQL to hand that would round down to a 15 minute interval and also round to nearest 15 minute interval. A quick search found several formulas but several had rounding errors. Here are both without any rounding errors.
declare @adate datetime = ‘2010/02/15 23:59:00’
— The epoch, or start of SQL Server time: ‘1900-01-01 00:00:00.000’
— select cast(0 as DateTime) as Epoch
— Both these formulas will only work until ‘5983-01-24 02:07:00.000’ !!
— select dateadd(n, 2147483647, cast(0 as DateTime))
— Round down to nearest 15 minute interval (avoiding any rounding issues)
select dateadd(n,(DATEDIFF(n, cast(0 as DateTime), @adate)/ 15) * 15, cast(0 as DateTime))
— Round to nearest 15 minute interval (avoiding any rounding issues)
select dateadd(n,((DATEDIFF(n, cast(0 as DateTime), @adate) + 7)/ 15) * 15, cast(0 as DateTime))
As noted, they have the limitation of working only until 5983 AD, but I figure I won’t be around!
If you use '20110101' instead of 0 for your date, you'll get extra years… 🙂
🙂 Lol, true, but I figured casting zero to datetime didn't incur any parsing overhead.
I was doing it this way:
DATEADD(MINUTE
,15 * (datediff(minute,0,CAST(datetime AS TIME))/15)
,CAST(CAST(DateTime AS DATE) AS SMALLDATETIME)
…which is as many years as you like, but I think I'll start using yours for the slightly reduced amount of casting.