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 :)
count(*) as RecordCount,
CAST(FLOOR(CAST(CreateDate AS FLOAT ))AS DATETIME) as DDMMYY
group by CAST(FLOOR(CAST(CreateDate AS FLOAT ))AS DATETIME)
Posted by Nick Holmes a Court @ 10:37 PM