select datetime by date and then by hour

[huddles2k]

Hello,

I have a datetime column (Col1), and I need to select * from table_1 where Col1 >=01/17/2003
group by date and then by hour increments

example:

Col1 Col2 Col3
02/02/2003 12:45:00 bob red
01/18/2003 23:45:02 tony blue
04/03/2003 17:45:00 Tony Green
02/02/2003 12:05:00 Phil Purple

I need to see
01/18/2003 00:00 - 00:59
.
.
.
01/18/2003 23:00 - 23:59
tony blue
02/02/2003 12:00 - 12:59
Phil Purple
Bob red

I would also like a count of records per hour by day

thanks

 

[zhavic]

This may do something like you want, but names will appear next to date and time ( not below ):

SELECT Col1, cTime, Col2, Col3
FROM ( SELECT CONVERT( char(10), Col1, 101 ) AS Col1,
CONVERT( char(2), DATEADD( hour, -1, Col1 ), 114 ) + ':00 - ' +
CONVERT( char(2), Col1, 114 ) + ':59' AS cTime,
Col2,
Col3
FROM table_1
) AS SubQuery
GROUP BY Col1, cTime, Col2, Col3


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[huddles2k]

zhavic,

thank you for the info. I used the following script and it does what I need to.

SELECT DateHour, COUNT(*) AS NumberOfRecords
FROM (SELECT CONVERT(char(10), SUBMIT_DTTM, 101) + ' ' + CONVERT(char(2), SUBMIT_DTTM, 8) + ':00' AS 'DateHour'
FROM INSTANT_CREDIT_APPLICATION) dt
GROUP BY DateHour
HAVING (DateHour >= CONVERT(DATETIME, '01/17/2003 00:00:00', 101))
ORDER BY DateHour

Huddles