Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

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: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

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_name
group by convert(char,eventdateandtime,101)
will do the trick.
DevX Pro
 
Comment and Contribute

 

 

 

 

 


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

 

 

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