SQL Server Group by Date


InterServer Web Hosting and VPS
Sony Playstation PS4 Pro Custom Build NVMe SSD Drive - Enclosure case and Crucial NVMe SSD
PS4 Pro Custom External SSD NVMe Drive

How I built a PS4 Pro Custom External SSD NVMe Drive...

NUC8i7BEH with memory
Intel NUC8I7BEH Review and Custom Build

This is my review of the Intel NUC8i7BEH and a summary of my build...

Card image cap
Asus M5A88-M EVO Review

This is my review of the Asus M5A88-M EVO motherboard...

Corsair Vengeance Blue 8GB (2x4GB) DDR3 PC3-12800C9 1600MHz Dual Channel Kit
Corsair Vengeance Blue Review

This is my review of the Corsair Vengeance Blue 8GB (2x4GB) DDR3 PC3-12800C9 1600MHz Dual Channel Kit memory...

Robotic Arm with USB PC Interface Review
Robotic Arm with USB PC Interface Review

This is my review of the Robotic Arm with USB PC Interface...

SQL Server Group by Date

Here we look at how create a query which groups by date in SQL Server.

This is usually useful when creating a report which can give you a daily total count for instance. We need a method of flooring the datetime into a date, that is removing the time part of the query, so we just have the date value.

In the below examples, I'll group the data by date, count how many items have that date, and then order by the items having the most items first. This is useful if you want to find out which days had the most activity, like which day was the most popular.

CAST as Date

In this example, we cast our datetime value as a date, which then allows us to group a datetime by it's date part:


SELECT CAST([CreatedDate] as Date), count(*)
FROM [dbo].[Pages]
GROUP BY CAST([CreatedDate] as Date)
ORDER BY COUNT(*) DESC


DATEDIFF and DATEADD

In this example, we use a combination of DATEDIFF and DATEADD to produce a floored date of the datetime.


SELECT DATEADD(dd, DATEDIFF(dd, 0, [CreatedDate]),0) , count(*)
FROM [dbo].[Pages]
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, [CreatedDate]),0)
ORDER BY COUNT(*) DESC


CONVERT to VARCHAR

In this example, we use a CONVERT to VARCHAR.


SELECT CONVERT(VARCHAR(10),[CreatedDate],112), count(*)
FROM [dbo].[Pages]
GROUP BY CONVERT(VARCHAR(10),[CreatedDate],112)
ORDER BY COUNT(*) DESC


So here the datetime is converted to a varchar using ISO standard yyyymmdd. The first problem I find with this method is in the execution plan, which reports a warning:


Type conversion in expression (CONVERT(varchar(10), [dbo].[Pages].[CreatedDate],112)) may affect "CardinalityEstimate" in query plan choice

Using SSMS, on 'Analyze Actual Execution Plan' the Showplan Analysis click for more information... shows:


One of the most important inputs for the Query Optimizer to choose an optimal execution plan is the estimated number of rows to be retrieved per operator. These estimations model the amount of data to be processed by the query, and therefore drive cost estimation. The models used by the process of estimating number of rows, called Cardinality Estimation, may have limitations. The accuracy of those models depends on how closely they correspond to the actual data distribution, correlation, chosen parameters, and how closely statistics, the main input for Cardinality Estimation, model all aspects of actual data.

In the real, what I've found that a 8th gen i7 laptop will handle this quite fast, but on deploying this to Azure, it slows down quite substantially, so one of the other methods is probably preferred. You'll have to see what works best with your data and your platform.

Sponsored Links
SolarSystem - AngularJS Demo
SolarSystem - Angular JS Demo

Check out my first Angular JS demo hosted in Azure, it helped win my first Angular contract. Put the sound on.