get hourly combine data

Question

Post reply

get hourly combine data

pat-261672

Hall of Fame

Points: 3325
More actions
January 27, 2009 at 10:49 am

#129836

I have data like this in one column and want to get the data by hour for 24 hours. how can i get it. I am trying to get date add (hour) but not working. the data is datetime and there is record for each second as you can see in this sample data!! Need to combine for the records for each hour
2009-01-22 21:57:04.767
2009-01-22 21:57:04.767
2009-01-22 21:57:04.767
2009-01-22 21:57:04.767
2009-01-22 21:57:04.767
2009-01-22 21:57:04.767
2009-01-22 21:57:04.767
2009-01-22 21:57:04.767
2009-01-22 21:57:04.767
2009-01-22 21:57:04.767
2009-01-22 21:57:04.767
2009-01-22 21:57:04.767
2009-01-22 21:57:04.767
2009-01-22 21:57:04.767
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:58:21.450
2009-01-22 21:59:44.900
2009-01-22 21:59:44.900
2009-01-22 21:59:44.900
2009-01-22 21:59:44.900
2009-01-22 21:59:44.900
2009-01-22 21:59:44.900
2009-01-22 21:59:44.900
2009-01-22 21:59:44.900
2009-01-22 21:59:44.900
2009-01-22 21:59:44.900
2009-01-22 21:59:44.900
2009-01-22 21:59:44.900
2009-01-22 21:59:44.900
2009-01-22 21:59:44.900

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

SELECT CONVERT(VARCHAR,@YOURDATECOLUMN,101),HOUR(YOURDATECOLUMN)

FROM YOURTABLE

group by CONVERT(VARCHAR,@YOURDATECOLUMN,101),HOUR(YOURDATECOLUMN)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

noeld SSC Guru Points: 96590 More actions · Answer 2

select convert( char(8), datecolumn, 112) dt, datepart( hour , datecolumn) as hr, count(*) cnt

from table

group by convert( char(8), datecolumn, 112) , datepart( hour, datecolumn)

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 3

noeld

SSC Guru

Points: 96590

January 27, 2009 at 10:58 am

#933389

Hehe Lowell beat me to it :d

* Noel

Lowell SSC Guru Points: 323518 More actions · Answer 4

lol I'm on the ball today...rare for me

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!