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))
  • maatthias

    That first query is very clever. Thanks for this.

  • Matt

    Thanks very much! Works like a charm.

  • http://mjoservices.com Phil Karras

    I’m sure there were many others like me who searched, found, & used your ideas to get a job done but then never said thanks.

    Thanks!

    Now I need to count how many values have been added to another field on a given date, I don’t care what the values are as long as they are not NULL.

  • http://www.jfelix.pt/ Joaquim FĂ©lix

    This example was great. I have been a Oracle (and PL/SQL) developer for last 9 years and now I’m struggling my way inside SQL Server (and T-SQL).
    Your post was, and is, a tremendous help.
    Thank You.

  • http://www.sarin.mobi/ James

    Great thanks mate. this made my day

  • http://ryancoder.blogspot.com Ryan Hennig

    I had a similar situation at work, where I needed to group by intervals of 10 minutes. I wrote a handy UDF to do this which can bucket datetime into any interval of minutes. You could use like this:

    SELECT dbo.BucketDateTime(GETDATE(), 60 * 24)

    The implementation is on my blog:
    http://ryancoder.blogspot.com/2010/06/bucket-datetimes-in-t-sql.html

  • Bill

    Thank You! 100x!

  • Pingback: haakondahl » SQL Query of the Day — Group By Year:()

  • Wilk Softwarre

    This solution help me with my test, the performance is incredible with other solution.
    (Poland)

  • Pingback: TSQL Tip O’ the Week – 8/2/11 – Records Per Day « SQL Feather and Quill()

  • http://www.smartbiller.biz Mikhail

    Another way:

    SELECT CAST(CAST(CRETS AS INT) AS DATETIME) as d,
    COUNT(*)
    FROM Table1 GROUP BY CAST(CAST(CRETS AS INT) AS DATETIME)
    order by d

  • MIke

    Thanks this is just what I was looking for..

  • Hafeez

    thanks a lot. it was a gr8 help today. stay blessed…

  • Lilium

    1 gap

  • Val

    Thanks this worked great!

  • sqlNewbie

    When I try the first method, I get an error that CRETS is invalid in the select list because it is not contained in the group by clause (even though it’s inside the dateadd function in the group by clause). How to fix this?