Nick Holmes a Court - SQL Reporting: Grouping by Day on a DateTime Field ,

Monday, May 7, 2012

SQL Reporting: Grouping by Day on a DateTime Field

Every few days i need some kind of adhoc report from one of our systems databases. Usually the information im trying to see is activity over time (hits, views, pageloads, messages, purchases, call logs, quotes, invoices, updates, etc etc).

What happens in most databases is that record entrys have a datetime field and the time component makes it difficult to easily group by day for reporting purposes.

I've had to solve this problem maybe 5k times in the last 10 years but every time i forget the SQL Syntax to round datetimes to days. So here it is.

Im writing this blog post purely to provide a record for myself so i don't have to search old database scripts each and every damn time :)

select
count(*) as RecordCount,
CAST(FLOOR(CAST(CreateDate AS FLOAT ))AS DATETIME) as DDMMYY
from Records
group by CAST(FLOOR(CAST(CreateDate AS FLOAT ))AS DATETIME)

Posted by Nick Holmes a Court @ 10:37 PM

Have your say!
Add a comment

2 Comments:

At June 1, 2012 at 8:22 AM, error1079 said...: hi............
What a useful post.I have a problem to record entrys have a datetime field and the time component but i solve all that after reading your post.Thanks for sharing.
At June 6, 2012 at 6:06 PM, Anonymous said...: I believe that you can also do one of the following (In SQL Server 2008 and later) with fewer instructions so maybe slightly quicker?

select
count(*) as RecordCount,
CAST(CAST(CreateDate AS INT )AS DATETIME) as DDMMYY
from Records
group by CAST(CAST(CreateDate AS INT)AS DATETIME)

OR

select
count(*) as RecordCount,
CAST(CreateDate AS DATE) as DDMMYY
from Records
group by CAST(CreateDate AS DATE)