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.

Column of different datetime values

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?

score 9 · Accepted Answer · edited May 23 '17 at 12:00

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.

Why the dateadd and the datediff functions, what good do they do in this scenario? — Brian Hvarregaard, Jun 13 '11 at 17:20
The datediff() calculates the number of Days difference between the DateCol value and a base date of 0. The dateAdd() adds that number of days to a base date of 0. The result of that calculation is that the DateCol's date value is preserved but its time value is discarded (or set to 0, if you prefer), so all the records with the same date will be grouped together regardless of the time-of-day part of the field. — Hellion, Jun 13 '11 at 17:27

Brian Webster · Answer 2 · 2011-06-13T17:18:47.047

I would do the Group By method (already posted as an answer by @Erik)

However, You can also use OVER and Partition By to accomplish this.

SELECT
    DISTINCT CONVERT(Date, VisitDate), 
    COUNT(*) OVER (PARTITION BY CONVERT(Date, VisitDate)) as Visitors -- Convert Datetime to Date
FROM
    MyLogTable
ORDER BY 
    CONVERT(Date, VisitDate)

It helps to convert your DateTime to a Date in these situations.
This is not as efficient as @Erik's solution; however, it's a good idea to learn the method, because you can do things with Over and Partition By that you can't do with Group By (at least, not efficiently). With that said, this is probably overkill for your situation.

TSQL counting how many occurrences on each day

2 Answers2