Search code examples
sqlsql-serverlogparser

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

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.

Solution

  • 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)