SQL Server, Analytics, .Net, Machine Learning, R, Python
Mitch Wheat has been working as a professional programmer since 1984, graduating with a honours degree in Mathematics from Warwick University, UK in 1986. He moved to Perth in 1995, having worked in software houses in London and Rotterdam. He has worked in the areas of mining, electronics, research, defence, financial, GIS, telecommunications, engineering, and information management. Mitch has worked mainly with Microsoft technologies (since Windows version 3.0) but has also used UNIX. He holds the following Microsoft certifications: MCPD (Web and Windows) using C# and SQL Server MCITP (Admin and Developer). His preferred development environment is C#, .Net Framework and SQL Server. Mitch has worked as an independent consultant for the last 10 years, and is currently involved with helping teams improve their Software Development Life Cycle. His areas of special interest lie in performance tuning
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!
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:
MSN, Email: mitch døt wheat at gmail.com