CODEWITHSUNDEEP
sqlsql-server
Omid Mafakher
Omid Mafakher

Reputation: 1387

How can I group by date time column for custom hour

How can I group by date time column for custom hour, for example every 5 Hour or every 12 Hour or every 24 hour.

Select CreateOn, Count(*) From Table1
Group By ?????

Upvotes: 1

Views: 169

Answers (2)

valex
valex

Reputation: 24134

You should select start date/time, '2011-01-01' in this example and then use DATEDIFF() function to get time difference in hours between start date and CreateOn. Then use / operator to get an integer interval number (5 hours in this example). 

Select 
min(DATEADD(HOUR,DATEDIFF (HOUR,'2011-01-01',CreateOn )/5*5,'2011-01-01')) 
   as Start_time,
max(DATEADD(HOUR,(DATEDIFF (HOUR,'2011-01-01',CreateOn )/5+1)*5,'2011-01-01')) 
   as End_Time,
DATEDIFF (HOUR,'2011-01-01',CreateOn )/5 as Interval_Number, 
Count(*) as _Count
From Table1
Group By DATEDIFF (HOUR,'2011-01-01',CreateOn )/5

Upvotes: 2

lmno
lmno

Reputation: 1044

If I understand your question correctly, you could try something like this:

SELECT CreateOn, Count(*) FROM Table1 GROUP BY (DATEPART(MINUTE, [Date]) % 12)

Upvotes: 1

Related Questions