sql query

[sriky]

I have timestamp as one of the fields and it's format is '09/29/1999 09:23:23AM'. the timestamp is in 10 minute intervals.

now, I have to group the data such that timestamp is in hourly interval.

for ex,

timestamp count abort
09/29/1999 08:53:23AM 2121 432

09/29/1999 09:03:23AM 242 4445

09/29/1999 09:13:23AM 5353 646
....


should appear as


timestamp count abort
09/29/1999 09:00AM 66263 6464

09/29/1999 10:00AM 103833 493027

09/29/1999 11:00AM 391015 340202



and so on...

so all the data should be grouped(i.e totalled) under hourly interval

can someone let me know

thanks

[Lothar Haensler]

OK; this is NOT your solution, but a starter:

select convert(varchar(14),datum,113), count(*) from wonkaftrg
group by convert(varchar(14),datum,113)

it is based on SQLServer, replace "WONKAFTRG" with your table name and "datum" by "timestamp".
It should give you an idea...

[sriky]

thanks for your response, but what is this 'convert' function?. I get an error message when I am trying to use this. I am using Access database.

Srikanth

[Lothar Haensler]

convert is an SQLServer function for converting any type of value to another type.
Unfortunately, I don't know how to convert an Access date to a string.