Thursday, February 17, 2011

 

TSQL: Round to Nearest 15 Minute Interval

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!


# posted by Mitch Wheat @ 5:13 pm

3 Comments:

If you use '20110101' instead of 0 for your date, you'll get extra years... :)

By Anonymous Rob Farley, at February 17, 2011 6:36 pm  

:) Lol, true, but I figured casting zero to datetime didn't incur any parsing overhead.

By Blogger Mitch Wheat, at February 18, 2011 11:50 am  

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.

By Blogger piers7, at March 02, 2011 12:04 pm  

Post a Comment



<< Home
    

Powered by Blogger