SQL Server Group by Count of DateTime Per Hour

Question

create table  Events       EventID int identity primary key      StartDate datetime not null      EndDate datetime not null   go insert into  Events  StartDate  EndDate  select  2007-01-01 12 44 12 AM    2007-01-01 12 45 34 AM  union all select  2007-01-01 12 45 12 AM    2007-01-01 12 46 34 AM  union all select  2007-01-01 12 46 12 AM    2007-01-01 12 47 34 AM  union all select  2007-01-02 5 01 08 AM    2007-01-02 5 05 37 AM  union all select  2007-01-02 5 50 08 AM    2007-01-02 5 55 59 AM  union all select  2007-01-03 4 34 12 AM    2007-01-03 4 55 18 AM  union all select  2007-01-07 3 12 23 AM    2007-01-07 3 52 25 AM     with apologies to http   www sqlteam com article working-with-time-spans-and-durations-in-sql-server for harvesting their base sql   I am trying to find the count of Events that occurred in an hour  so the result set would look like this   2007-01-01      12 00     3 2007-01-02       5 00     2 2007-01-03       4 00     1 2007-01-07       3 00     1   I have been playing with dateadd and round and grouping but not getting it  Can anyone help   Thanks

User · Answer

Alternatively  just GROUP BY the hour and day   SELECT  CAST Startdate as DATE  as  StartDate            CAST DATEPART Hour  StartDate  as varchar      00  as  Hour            COUNT    as  Ct  FROM  Events GROUP BY CAST Startdate as DATE   DATEPART Hour  StartDate  ORDER BY CAST Startdate as DATE  ASC   output   StartDate   Hour    Ct 2007-01-01  0 00    3 2007-01-02  5 00    2 2007-01-03  4 00    1 2007-01-07  3 00    1

User · Answer

How about this  Assuming SQL Server 2008   SELECT CAST StartDate as date  AS ForDate         DATEPART hour StartDate  AS OnHour         COUNT    AS Totals FROM  Events GROUP BY CAST StartDate as date          DATEPART hour StartDate    For pre-2008   SELECT DATEADD day datediff day 0 StartDate  0    AS ForDate         DATEPART hour StartDate  AS OnHour         COUNT    AS Totals FROM  Events GROUP BY CAST StartDate as date          DATEPART hour StartDate    This results in    ForDate                   OnHour   Totals ----------------------------------------- 2011-08-09 00 00 00 000     12       3

User · Answer

You can also achieve this by using following SQL with date and hour in same columns and proper date time format and ordered by date time  SELECT  dateadd hour  datediff hour  0  StartDate   0  as  ForDate        COUNT    as  Count   FROM  Events GROUP BY dateadd hour  datediff hour  0  LogTime   0  ORDER BY ForDate

User · Answer

I found this somewhere else  I like this answer   SELECT  Hourly   COUNT    as  Count    FROM    SELECT dateadd hh  datediff hh   20010101    date created     20010101   as  Hourly      FROM table  idat  GROUP BY  Hourly

[sql-server] SQL Server Group by Count of DateTime Per Hour?

Examples related to sql-server

Examples related to datetime

Examples related to rounding