Grouping datetime by date in T-SQL

Posted by Andreas on November 8, 2007 Leave a comment (11) Go to comments

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))
SQL Server, T-SQL, ,
  • 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!

  • http://haakondahl.com/blog/2011/04/30/sql-query-of-the-day-group-by-year/ haakondahl » SQL Query of the Day — Group By Year:

    [...] fellow has a blog, and it’s good: Grouping datetime by date in T-SQL « SQL Disco.  Many times in SQL you need to group by year, or by date, and what you have to work with is a big [...]

  • Wilk Softwarre

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

  • http://www.sqlfeatherandquill.com/2011/08/02/tsql-tip-o-the-week-8211-records-per-day/ TSQL Tip O’ the Week – 8/2/11 – Records Per Day « SQL Feather and Quill

    [...] I found an article from SQL Disco here : Grouping datetime by date in T-SQL [...]

  • 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