Grouping/Counting by Date
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
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:
Demonstration of the string conversion technique:
Examples of use within grouping statements: