Group by 24 hour and Day of week - time interval

Question

I know this is a common question, but I couldn't find something that matches my case. So here we go.

I have a table named RECORDED_ENTRIES that has columns ID (int) and CreateDate (datetime).

I want to group data by hour on a 24hour basis regardless of the actual date.

For example I want the output to be:

00:00 - 2
01:00 - 3
02-00 - 12
....
23:00 - 1

The aim is to create data for a chart that will display recorded entries by the hour of day

I need to group also data by the name of Day.

Thanks

Is grouping by DAY good enough? or does it have to be an exact 24 hour timespan from the current time? — Steven Ryssaert, Apr 09 '13 at 09:24
actually i would need both :) I am asking for the 24hour interval and then I guess i can figure out how the grouping by day would work. But any help is appreciated — MaVRoSCy, Apr 09 '13 at 09:29
for the above question, i don't need the grouping by date. You see , i have entries recorded in various days. So i want to get the trend (in which time of the day most entries are recorded etc) — MaVRoSCy, Apr 09 '13 at 09:31

score 2 · Accepted Answer · answered Apr 09 '13 at 09:30

2

What you want is:

GROUP BY DATEPART(HH,CreateDate)

SQLFiddle

answered Apr 09 '13 at 09:30

Nenad Zivkovic

18,221
6
42
55

Oh yes, that's what i want!!. What about grouping the results by the name of day instead of by hour? – MaVRoSCy Apr 09 '13 at 09:37
@MaVRToSCy Please, check out DATEPART function. It has all options to get different parts of Datetime - http://msdn.microsoft.com/en-us/library/ms174420.aspx – Nenad Zivkovic Apr 09 '13 at 09:38
@MaVRoSCy DATEPART(weekday,CreateDate) or DATEPART(dw,CreateDate) is for day of the week. The return value depends on the value that is set by using SET DATEFIRST. (Whatever 1 is Sunday or Monday) – Nenad Zivkovic Apr 09 '13 at 09:42
1

Thanks a lot, I found something useful here http://stackoverflow.com/questions/12533/how-do-you-convert-the-number-you-get-from-datepart-to-the-name-of-the-day – MaVRoSCy Apr 09 '13 at 09:43

score 0 · Answer 2 · answered Apr 09 '13 at 09:37

You can use the CONVERT() function in sql server which acts like CAST() but you can use styles! convert(varchar(2),,108) will use the 108-code which is HH:MM:SS. Check out http://www.sql-server-helper.com/tips/date-formats.aspx

Example:

DECLARE@d DATETIME= '2013-03-02 19:45';
SELECT
@d AS dt
,CONVERT(VARCHAR(2),@d,108) AS theHour

Group by 24 hour and Day of week - time interval

2 Answers2