devxlogo

Calculating daily stats from DateTime column

Calculating daily stats from DateTime column

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.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist