Tag Archives: Datetime

Grouping datetime by date in T-SQL

In reporting it is very common to group a datetime by date / day. There are several ways to do this, and I have done some testing on a large table I have. Listed here are four ways, from best to worst. The performance increase by changing a query from the worst (Which I have seen being used several times!) to the best might be the difference between a query taking 10 minutes and a query taking 10 seconds. CRETS is a datetime column.

--Use this: :)
SELECT DATEADD(dd,(DATEDIFF(dd,0,CRETS)),0),
COUNT(*)
FROM Table1 GROUP BY DATEADD(dd,(DATEDIFF(dd,0,CRETS)),0)
 
SELECT CAST(DATEDIFF(dd,0,CRETS) AS Datetime),
COUNT(*)
FROM Table1 GROUP BY CAST(DATEDIFF(dd,0,CRETS) AS Datetime)
 
SELECT DATEPART(dd,CRETS) AS [Day],
DATEPART(mm,CRETS) AS [Month],
DATEPART(yyyy,CRETS) AS [Year],
COUNT(*)
FROM Table1
GROUP BY DATEPART(dd,CRETS), DATEPART(mm,CRETS), DATEPART(yyyy,CRETS)
 
--Don't use this:
SELECT CONVERT(Datetime, CONVERT(NCHAR(10), CRETS, 121)),
COUNT(*)
FROM Table1
GROUP BY CONVERT(Datetime, CONVERT(nchar(10), CRETS, 121))