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))
That first query is very clever. Thanks for this.
Thanks very much! Works like a charm.
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.
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.
Great thanks mate. this made my day
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