Grouping/Counting by Date

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 2001

Description

Probably the most common investigative query that I run is a grouping/count of records by date. This kind of query is valid across many different types of data, whether it be the tracking of rate of customer signups, web page hits, or TSQL commands from a profiler trace table, the technique that I use is simple and easy to remember.

TSQL has many built-in date and time functions, but for aggregation, they must be called inline, and can get a bit verbose.

The CONVERT function has a very useful formatting feature when DATETIME data is being converted to STRING. There are many different preset formats that can be used, but my preference is for format code 113 (UK with 4-digit year: dd MMM yyyy hh:mm:ss).

This format preset leaves no room for dd/mm - mm/dd confusion (especially useful in preserving my coding accuracy during my transition from living in UK to living in Canada).

Microsoft has indicated in the past that the CONVERT function is on the deprecation list, but several versions later it is still there, so I can see this being a viable technique for a few years yet.

Basically, I use the CONVERT function to transition a data from DATETIME to VARCHAR and back again via the formatting function.

The usefulness comes when utilizing specific length strings in the transition, as this effectively truncates/zeroes out the right hand part of the string. It is probably better explained by reading the examples in the code below:

Code

Demonstration of the string conversion technique:

DECLARE @Datetime DATETIME

SELECT @Datetime = '2012-06-25 13:46:09.927'

/**** a full length formatted date string ***/

SELECT CONVERT(VARCHAR(24), @Datetime, 113)

-- Formatted Intermediate Output: 25 Jun 2012 13:46:09:927

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(24), @Datetime, 113))

-- Native DATETIME Final Output: 2012-06-25 13:46:09.927

/**** date rounded to the second ***/

SELECT CONVERT(VARCHAR(20), @Datetime, 113)

-- Formatted Intermediate Output: 25 Jun 2012 13:46:09

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(20), @Datetime, 113))

-- Native DATETIME Final Output: 2012-06-25 13:46:09.000

/*

NOTE the inclusion of numeric strings to complete the datetime string in the remaining cases

It is not always necessary, as conversions that omit minutesi will 'fill in the blanks'

but is included in all cases for clarity and insurance against changes in implicit conversion rules

*/

/**** date rounded to the minute ***/

SELECT CONVERT(VARCHAR(17), @Datetime, 113) + ':00'

-- Formatted Intermediate Output: 25 Jun 2012 13:46

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(17), @Datetime, 113) + ':00')

-- Native DATETIME Final Output: 2012-06-25 13:46:00.000

/**** date rounded to the hour ***/

SELECT CONVERT(VARCHAR(14), @Datetime, 113) + ':00:00'

-- Formatted Intermediate Output: 25 Jun 2012 13

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(14), @Datetime, 113) + ':00:00')

-- Native DATETIME Final Output: 2012-06-25 13:00:00.000

/**** date rounded to the day ***/

SELECT CONVERT(VARCHAR(11), @Datetime, 113) + ' 00:00:00'

-- Formatted Intermediate Output: 25 Jun 2012 13

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(11), @Datetime, 113) + ' 00:00:00')

-- Native DATETIME Final Output: 2012-06-25 13:00:00.000

Examples of use within grouping statements:

/*

This leads to the easy transformation into a grouping command

Note the use of the conversion of the string back to datetime

This is essential for ordering purposes but is also invaluable

in helping to identify syntactic/semantic errors in the code

(conversion to a string will always work but may not produce a

valid date string)

*/

/**** grouped by hour ***/

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(14), <DateColumn>, 113) + ':00:00'), COUNT(*)

FROM <TableName> (NOLOCK)

GROUP BY CONVERT(DATETIME, CONVERT(VARCHAR(14), <DateColumn>, 113)+ ':00:00')

ORDER BY CONVERT(DATETIME, CONVERT(VARCHAR(14), <DateColumn>, 113)+ ':00:00') DESC

/**** built-in date functions can also be used to augment the grouping level ***/

/**** but it does get a bit messier..... ***/

/**** grouped by half-hour ***/

SELECT CASE

WHEN DATEPART(mi, <DateColumn>) < 30 THEN CONVERT(DATETIME, CONVERT(VARCHAR(14), <DateColumn>, 113) + ':00:00')

ELSE CONVERT(DATETIME, CONVERT(VARCHAR(14), <DateColumn>, 113) + ':30:00')

END,

COUNT(*)

FROM <TableName> (NOLOCK)

GROUP BY CASE

WHEN DATEPART(mi, <DateColumn>) < 30 THEN CONVERT(DATETIME, CONVERT(VARCHAR(14), <DateColumn>, 113) + ':00:00')

ELSE CONVERT(DATETIME, CONVERT(VARCHAR(14), <DateColumn>, 113) + ':30:00')

END

ORDER BY CASE

WHEN DATEPART(mi, <DateColumn>) < 30 THEN CONVERT(DATETIME, CONVERT(VARCHAR(14), <DateColumn>, 113) + ':00:00')

ELSE CONVERT(DATETIME, CONVERT(VARCHAR(14), <DateColumn>, 113) + ':30:00')

END DESC