Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

time in SQL


I wrote a query that works and does as expected. One of pieces of
information I have is the date/time in the table. I want to find out how
many times during the hour the event occurs.

The format in the table for date/time is:

2007-06-01 16:15:18.000
2007-06-01 16:32:00.000
2007-06-01 17:14:25.000
2007-06-01 17:20:55.000
2007-06-02 17:33:17.000
2007-06-02 20:32:49.000

Ideally how do I figure out that with the times above that there were

16th hour 2 events logged
16th hour 3 events logged
20th hour 1 events logged

Basically I neeed to sum up the number of events by the hour.

Any help is appreciated.

Use DATEPART .

E.g.

select count(1), datepart(hh, datefield)  from DataTable group by
datepart(hh, datefield)

"Big D" <BigDa@newsgroup.nospam> wrote in message

news:OgfjasGqHHA.4100@TK2MSFTNGP04.phx.gbl...

Here is an example of truncating a datetime to the previous hour,
grouping on it, and counting the resulting rows.

SELECT dateadd(hour, datepart(hour,crdate),
               dateadd(day,datediff(day,0,crdate),0)),
       count(*) as Rows
  FROM sysobjects
 GROUP BY dateadd(hour, datepart(hour,crdate),
               dateadd(day,datediff(day,0,crdate),0))

Roy Harvey
Beacon Falls, CT

On Wed, 6 Jun 2007 14:47:14 -0400, "Big D" <BigDa@newsgroup.nospam>
wrote:

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc