SQL Server Group by Count of DateTime Per Hour?

by

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

More Related Contents:

  1. Undesired rounding of DateTime in SQL Server
  2. Sql Server string to date conversion
  3. How to get a float result by dividing two integer values using T-SQL?
  4. How to convert SQL Server’s timestamp column to datetime format
  5. How can I truncate a datetime in SQL Server?
  6. What is the significance of 1/1/1753 in SQL Server?
  7. How to get Time from DateTime format in SQL?
  8. How to get first and last day of previous month (with timestamp) in SQL Server
  9. How to remove the time portion of a datetime value (SQL Server)?
  10. SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  11. Date conversion and culture: Difference between DATE and DATETIME
  12. SQL Query to find the last day of the month
  13. Handling date in SQL Server
  14. How can I get the sum of multiple datetime values?
  15. Comparing dates stored as varchar
  16. Add default value of datetime field in SQL Server to a timestamp
  17. Best way to store time (hh:mm) in a database
  18. Retrieving the date in SQL Server; CURRENT_TIMESTAMP vs GetDate()
  19. Why SQL Server DATETIME type saves time in ticks of 1/300 of a sec?
  20. SQL Server: convert ((int)year,(int)month,(int)day) to Datetime [duplicate]
  21. Convert a string with ‘YYYYMMDDHHMMSS’ format to datetime
  22. Convert UTC Milliseconds to DATETIME in SQL server
  23. How does SQL Server decide format for implicit datetime conversion?
  24. How to convert text column to datetime in SQL
  25. How to calculate difference in hours (decimal) between two dates in SQL Server?
  26. What do Clustered and Non-Clustered index actually mean?
  27. Regex pattern inside SQL Replace function?
  28. How can I round up the time to the nearest X minutes?
  29. How do I flush the PRINT buffer in TSQL?
  30. How to do Sql Server CE table update from another table

Leave a Comment