how to group a query by month-year of a datetime column in SSMS?

Hi Experts,
What is the best way to do that in SSMS and have:
1- The best performance.
2- The easiest to read.
LVL 5
bfuchsAsked:
Who is Participating?
 
HuaMin ChenSystem AnalystCommented:
Try
...
group by replace(substring(CONVERT(varchar,date_column,113),4,8),' ','-')

Open in new window

0
 
HuaMin ChenSystem AnalystCommented:
Try
..
group by substring(convert(varchar,date_column,112),1,6)

Open in new window

0
 
HuaMin ChenSystem AnalystCommented:
The above is to group by "YYYYMM".
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bfuchsAuthor Commented:
Hi,

Is it possible to have.
1- month before year.
2- a separator between them, like Jan-15

Thanks,
Ben
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What about posting here the query?
It will be more easy to help you with query so we don't need to guess.
0
 
Scott PletcherSenior DBACommented:
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, datetime_column), 0), ...
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, datetime_column), 0)

That is a purely mathematical calculation.  Anything that converts to strings will be (much) slower.
0
 
bfuchsAuthor Commented:
Hi Experts,

Just got a chance to test them.

@HuaMinChen,
Yours work fine, just waiting to see if this the most efficient way in my case.

@Scott,
below is the format I get, however in my case, I cannot rely on the FE to do the formatting, do you have another efficient suggestion that would return the MMM-YY format ?
2006-11-01 00:00:00.000

Thanks,
Ben
0
 
Scott PletcherSenior DBACommented:
You can force the reformatting to be after the grouping is done, for best efficiency, by coding it like this:

SELECT STUFF(CONVERT(varchar(30), DATEADD(MONTH, DATEDIFF(MONTH, 0, datetime_column), 0), 7), 4, 5, '-') , ...
FROM ...
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, datetime_column), 0)
0
 
bfuchsAuthor Commented:
Hi Experts,

Testing both versions below in a table containing close to 1M records, and they both take the exact amount of time to execute (7 Sec.)

1-replace(substring(CONVERT(varchar,date_column,113),4,8),' ','-')
2-STUFF(CONVERT(varchar(30), DATEADD(MONTH, DATEDIFF(MONTH, 0, datetime_column), 0), 7), 4, 5, '-')

Can I assume it will not make a difference in my case which one is being used?
0
 
Scott PletcherSenior DBACommented:
I guess so.  In style #2, did you leave the GROUP BY with just the DATEADD( part and not the STUFF(CONVERT(?
0
 
bfuchsAuthor Commented:
Yes, I realized that.
BTW, the 7 sec it takes to execute is only when selecting all records, but when grouping it, it takes less than a second.
0
 
bfuchsAuthor Commented:
Thank you!
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.

All Courses

From novice to tech pro — start learning today.