Sql Server Programming :: time in SQL


	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... - Hide quoted text - >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. 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: - Hide quoted text - >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.