• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1949

# DateTime Group By question

I am writing a report and the users and they would like to select the grouping of the report.

All the grouping will be on a single datetime field (oddly enough named datetime).

They would like to be able to choose if the report is grouped by half hour, day, week, or month.

I can accomplish day by using:
GROUP BY CONVERT(VARCHAR(10),DateTime, 110)

But how can I accomplish half hour, week, or month?

Many thanks for the help.
0
sifuhall
• 2
• 2
1 Solution

Commented:
Group By DatePart(Week, Datetime)

Group By DatePart(Month, Datetime)
0

Commented:
Without giving you a red carpet solution heres some clues

select convert(float, getdate())

returns a VB style date number

You can then round this off with number arithmetic

You can convert these numbers back into dates using the convert function
0

Commented:
Group By DatePart(Week, [Datetime])

Group By DatePart(Month, [Datetime])

Group by Year([DateTime]), Month([DateTime]), Day([DateTime]), DatePart(Minute, [DateTime])/30
0

Commented:
thats a lot easier than my suggestion !!!
0

Commented:
I'm assuming by your example for day that when you say group by week you would not want similar weeks form different years grouped together.  If this is the case then you should look into BOL under "datepart"

some examples:
--group by week
group by datepart(year,[datetime])
,datepart(month,[datetime])

alternatively if you, for some reason, do not want to use subgroups you can build your group by column
examples :
--group by month
group by 100*datepart(year,[datetime])+datepart(month,[datetime])

--group by week
group by 100*datepart(year,[datetime])+datepart(week,[datetime])

--group by day
group by 1000*datepart(year,[datetime])+datepart(dayofyear,[datetime])

--group by hour
group by 1000000*datepart(year,getdate())+1000*datepart(dayofyear,getdate()) + 10* datepart(hour,getdate())

-- Half Hour is a little different since there is no native datpart with that name
group by 1000000*datepart(year,getdate())+1000*datepart(dayofyear,getdate()) + 10* datepart(hour,getdate()) + datepart(minute,getdate())%30

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.