TSQL counting how many occurrences on each day

Question

I have a table that registers visitors to a website. I want to count how many have visited my website on each day.

My problem is that I can't figure out how to typecast the datetime value so that it doesn't use the entire date field when making a distinct count.

Can anyone explain this?

Answer 1

SELECT
   DateWithNoTimePortion = DateAdd(Day, DateDiff(Day, '19000101', DateCol), '19000101'),
   VisitorCount = Count(*)
FROM Log
GROUP BY DateDiff(Day, 0, DateCol);

For some reason I assumed you were using SQL Server. If that is not true, please let us know. I think the DateDiff method could work for you in other DBMSes depending on the functions they support, but they may have better ways to do the job (such as TRUNC in Oracle).

In SQL Server the above method is one of the fastest ways of doing the job. There are only two faster ways:

• Intrinsic int-conversion rounding :

  Convert(datetime, Convert(int, DateCol - '12:00:00.003'))
  

• If using SQL Server 2008 and up, this is the fastest of all (and you should use it if that's what you have):

  Convert(date, DateCol)
  

When SQL Server 2008 is not available, I think the method I posted is the best mix of speed and clarity for future developers looking at the code, avoiding doing magic stuff that isn't clear. You can see the tests backing up my speed claims.