Group Query based on hour from Datetime field

[bryandj23]

Hi All.

I have a database storing data along with a Datetime stamp for when the record was created. The data is accessed via ASP pages, but I'm building my query in Query Analyzer.

I need to develop a query to count, sum and avg the data based on the hour of the day. (How many rows of data for the 9 o'clock hour, how many rows for the 10, etc).

I'm using the following select statement to strip away unneeded characters from the Datetime field:

select distinct(left(convert(varchar,Date,114),2)) as [TimeOfDay]

This successfully gives me my "hours", but I'm unable to say:

Group by [TimeofDay]

SQL pukes. If I change to

Group by Date

along with

Order by [TimeofDay]

I do see results, however the "Time of Day" is repeated, as if the DISTINCT clause was missing.

How can I Group the output by just the hour of day?

Thanks in Advance!

Bryan

 

[gmmastros]

select DatePart(Hour, Date) as [TimeOfDay]
....
....
Group By DatePart(Hour, Date)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

select datepart(hh,getdate())

group by datepart(hh,getdate())


change getdate() to your datetime column



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bryandj23]

Thanks George, Denis. I knew it had to be something simple.

That was almost too simple. Unfortunately the only one book I have on SQL is a little too "beginnerish".