query for you experts, is it possible?

What I am trying to do sounds very simple, yet has been very annoying at the same time.

My tbl [signups], has a column [users], and [signupdate]
[users] are unique IDs, and [signupdate] is in datetime format.

What would be the easiest most efficient way to return how
many people signed up per hour?
IE.

hour signupCount
0 4
1 3
2 1
3 1

ect,,,,,,,,,


I have it working w/ loops and a temp tbl, just wondering if there is an easier way

thanks!

What DB are you using?

If it was in Postgres I'd use the to_char function on the timestamp to extract the specific date/hour and group by that.

For example (*note untested code)

SELECT count(users) AS count, to_char(signupdate, 'MM-DD-YYYY HH24') AS date GROUP BY date .

HTH,

-b

Thanks for the Suggestion, but I found what I was try to accomplish which is :


SELECT CONVERT(VARCHAR(32), signupDate, 101) theDate, DATEPART(hh, signupDate) SignUpHour, COUNT(*) HourCount

FROM memberSignups
WHERE signupDate >= '2003-07-01' AND signup_date < '2003-07-02'
GROUP BY CONVERT(VARCHAR(32), signupDate, 101), DATEPART(hh, signupDate)