group by and sort by datepart(month)

Question

group by and sort by datepart(month)

mvander

Old Hand

Points: 389
More actions
May 2, 2004 at 6:12 pm

#107901

I have a table that contains project information, with one column called
EndDate of DateTime data type. I am trying to create a SQL Query that
will Group By and Sort By the the year portion of the EndDate and the
month portion of the EndDate. I'm going to use the dataset to graph the
number of projects for each month. When I use the DatePart function on the
EndDate column for the month, the month is returned as an integer. So
January = 1, Feb = 2, etc. However, now when I sort on this, the sort
comes out 2004/1 (for Jan), 2004/10 (for Oct), 2004/11 (for Nov), 2004/12 (for Dec) and then
2004/2 (for Feb).
I was wondering if anyone knew how to force the DatePart function to
return a 2 place integer, with the leading 0, so that the months
will sort in the correct order? Or some other way to do this Group By and Sort By?

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

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

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

I don't think there is a way to add a leading 0 to the month unless you want to use convert function and concatnate with a 0 in front, which may not makes sense if you are using it in GROUP by or for that matter ORDER BY clause, but what I am wondering is datepart will return in INT datatype so the order by sorting should not give a problem.

Thanks

Prasad Bhogadi
www.inforaise.com

Johan Bijnens SSC Guru Points: 135377 More actions · Answer 2

select year(datetimecolumn) as Year_datetimecolumn

, month(datetimecolumn) as Month_datetimecolumn

, count(*) as Counter

from mytable

group by year(datetimecolumn)

, month(datetimecolumn)

order by Year_datetimecolumn, Month_datetimecolumn

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

mvander Old Hand Points: 389 More actions · Answer 3

Thanks, alzdba. I was trying to use a Convert statement with concantenation to get the year and month into one column like '2004/1'. I guess that was the error of my ways.

Caine Schneider Old Hand Points: 355 More actions · Answer 4

You could also use CONVERT(char(7), date_col, 120) to return a value such as 2004-05.

Caine