Date and Time - 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



ċ
Date and Time grouping.sql
(3k)
Andy Hughes,
Jun 25, 2012, 11:23 AM
Comments