If you are familiar with Microsoft Log Parser you probably recognize the Quantize function which will truncate a value to the nearest multiple of another value. It is quite handy for grouping date-time fields into increments.
Date-Time Count
1/1/2010 00:00 100
1/1/2010 00:15 134
1/1/2010 00:30 56
....
I'm trying to find a similar function in Transaction-SQL (specifically SQL Server 2005 or 2008) that will allow me to do a similar grouping on date-time.
You can round to any given number of minutes like so:
DateAdd(Minute, (DateDiff(minute, 0, getutcdate() )/15) * 15, 0)
Instead of using getutcdate() you can use your date column, variable or expression. In addition the number of minutes can be a variable.
declare @minutesQuantize int set @minutesQuantize = 15
DateAdd(Minute, (DateDiff(minute, 0, getutcdate() )/@minutesQuantize) * @minutesQuantize, 0)
The only rule is that the date difference must fit into an integer, I.e. be less than 2 billion. That means you can't do seconds or milliseconds without a more complicated expression.
If you need seconds or milliseconds do this:
dateadd(ms, (datediff(ms, dateadd(day, datediff(day, 0, @date), 0), @date)/@msInterval)*@msInterval, dateadd(day, datediff(day, 0, @date), 0))
Or, if you want to wrap this into a function:
create function dbo.DateRoundMinutes(@dt datetime, @minutes int)
returns datetime
as begin
return DateAdd(Minute, (DateDiff(minute, 0, @dt )/@minutes) * @minutes, 0)
end
go
create function dbo.DateRoundMilliseconds(@dt datetime, @ms int)
returns datetime
as begin
return dateadd(ms, (datediff(ms, dateadd(day, datediff(day, 0, @dt), 0), @dt)/@ms)*@ms, dateadd(day, datediff(day, 0, @dt), 0))
end
Which you can use like this:
select t.dt,
dbo.DateRoundMilliseconds(dt, 500) dt0_5Second, -- Half second
dbo.DateRoundMilliseconds(dt, 5000) dt5second, -- 5 second
dbo.DateRoundMilliseconds(dt, 15000) dt15Second,
dbo.DateRoundMilliseconds(dt, 90000) dt90Second,
dbo.DateRoundMinutes(dt, 2) dt2Minute,
dbo.DateRoundMinutes(dt, 5) dt5Minute,
dbo.DateRoundMinutes(dt, 15) dt15Minute,
dbo.DateRoundMinutes(dt, 90) dt90Minute
from
/* some table having a column dt */
Not directly, it doesn't. But you can group by a function (that you write) that rounds the datetime column to its nearest quarter-hour (or whatever Quantize does).
SELECT
dbo.QuarterHour(DateColumn) AS Date-Time
, COUNT(*) AS Count
FROM MyTable
GROUP BY dbo.QuarterHour(DateColumn)
