This weeks tip:
Often times I’ll need to count how many records are added to a table based on the date. To do this, I use the GROUP BY and the CONVERT function.
The idea is to convert the datetime to just a date, then group and count the data by the date.
The Set Up
Here’s an example I got from this post, slightly altered:
SET NOCOUNT ON
Go
Create Table #RandomDates (DateTest datetime)
Go
Declare @Counter int
Set @Counter = 0
While @Counter < 1000
Begin
Insert #RandomDates (DateTest)
Select Dateadd(D, (-1)* ROUND((RAND() * (10)),0), Getdate()) --Random Date 0 to 10 days old
Set @Counter = @Counter + 1
End
The Example
Select Convert(date, DateTest) as 'Date',
Count(Convert(date, DateTest)) as 'Count'
from #RandomDates
Group by Convert(date, DateTest)
Order by Convert(date, DateTest)
Here are my results – yours will differ as the randomness implies
Here are some related posts from the intraweb:
- I found an article from SQL Disco here : Grouping datetime by date in T-SQL
- A wish from SQL 2005 get a Date function like this from Aaron Johnson here: SQL Server Group By Datetime