Question:
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:
EventDateandTime------------------Feb 4 1999 5:37PMFeb 4 1999 6:14PMFeb 5 1999 2:00AM
My query would return:
Date Count---------- -----1999-02-04 21999-02-05 1
Answer:
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_namegroup by convert(char,eventdateandtime,101)
will do the trick.
Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
