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 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
Try
..
group by substring(convert(varchar,date_column,112),1,6)

Open in new window

0
HuaMin ChenProblem resolverCommented:
The above is to group by "YYYYMM".
0
bfuchsAuthor Commented:
Hi,

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

Thanks,
Ben
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

HuaMin ChenProblem resolverCommented:
Try
...
group by replace(substring(CONVERT(varchar,date_column,113),4,8),' ','-')

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.