Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL Server
Expertise: Beginner
May 11, 1999



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Calculating daily stats from DateTime column

I have a table in SQL Server 6.5 with a datetime column that is used for tracking each time an event occurs; the date and time data are combined into one column. I want to write a query to count up the number of events that occurred each day. So, for example, if my data looked like this:

Feb 4 1999  5:37PM
Feb 4 1999  6:14PM
Feb 5 1999  2:00AM
My query would return:

Date                Count
----------          -----
1999-02-04              2
1999-02-05              1

So, you want a summary of the number of events each day.

It seems like a SELECT with a GROUP BY should answer all your questions. However, you don't want to group by the date and time, but only by the date.

One easy way is to use the convert function to convert the datetime to a format that just displays the date.

select convert(char,eventdateandtime,101),count(*) 
from table_name
group by convert(char,eventdateandtime,101)
will do the trick.
DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date