Does Transact-SQL have a similar function for MS Logparser Quantize?

Question

If you are familiar with Microsoft Log Parser, you will probably recognize the Quantize function, which truncates the value to the nearest multiple of another value. This is very convenient for grouping date and time fields in increments.

Date-Time              Count
1/1/2010 00:00         100
1/1/2010 00:15         134
1/1/2010 00:30         56
....

I am trying to find a similar function in Transaction-SQL (in particular, SQL Server 2005 or 2008) that will allow me to do a similar grouping in a date.

Answer 1

Not directly, it is not. But you can group a function (which you write) that rounds the datetime column to the next quarter hour (or something that Quantize does).

SELECT
    dbo.QuarterHour(DateColumn) AS Date-Time
  , COUNT(*) AS Count
FROM MyTable
GROUP BY dbo.QuarterHour(DateColumn)

Answer 2

, :

DateAdd(Minute, (DateDiff(minute, 0, getutcdate() )/15) * 15, 0)

getutcdate() , . , .

declare @minutesQuantize int set @minutesQuantize = 15
DateAdd(Minute, (DateDiff(minute, 0, getutcdate() )/@minutesQuantize) * @minutesQuantize, 0)

, , I.e. 2 . , .

, :

dateadd(ms, (datediff(ms, dateadd(day, datediff(day, 0, @date), 0), @date)/@msInterval)*@msInterval, dateadd(day, datediff(day, 0, @date), 0))

, :

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

:

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 */