Can I convert a datetime to just a year/month for a group by?

Question

Post reply

Can I convert a datetime to just a year/month for a group by?

Sean Grebey-262535

SSCommitted

Points: 1724
More actions
July 2, 2010 at 12:31 pm

#226409

I need to group records by the month/year in a select statement, but the field i have to base it off of it a datetime field. Is there a way to convert that to just a month and year for my group by? Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

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

sturner SSC-Insane Points: 22411 More actions · Answer 1

simplest way is to do something like:

group by (datepart(year,date)*100 ) + datepart(month,date)

The probability of survival is inversely proportional to the angle of arrival.

Sean Grebey-262535 SSCommitted Points: 1724 More actions · Answer 2

Sean Grebey-262535

SSCommitted

Points: 1724

July 2, 2010 at 1:46 pm

#1188410

Thanks for the help.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

There are lots of variations that will work.

Here's another that gives you a six-character string in the format YYYYMM.

SELECT LEFT(CONVERT(VARCHAR,GETDATE(),112),6) as YrMth

Just substitute your date column for getdate().

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

You could also just convert the datetime to the first day of the month at time 00:00:00.000 and group by that:

group by dateadd(mm,datediff(mm,0,MyDate),0)

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 5

The Dixie Flatline (7/2/2010)
There are lots of variations that will work.
Here's another that gives you a six-character string in the format YYYYMM.
SELECT LEFT(CONVERT(VARCHAR,GETDATE(),112),6) as YrMth
Just substitute your date column for getdate().

Don't need the LEFT here - you can do this:

SELECT CONVERT(CHAR(6), GETDATE(), 112)

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

So you can. 🙂

I'll remember that. Thank you, Jeffrey.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills