How to extract month and year(combined) from a date

Question

How to extract month and year(combined) from a date

Nuts

SSCrazy

Points: 2547
More actions
July 19, 2007 at 9:06 pm

#173854

Hi Guys
I have got a date for ex '1/11/2005' I need to create a separate column called 'month&year' which should display '11/2005'
And I also need to create another column called quarter which should display the current quarter for the date for ex in this case, it should display 4.

Cheers

Viewing 9 posts - 1 through 8 (of 8 total)

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

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

This one is pretty easy to figure out if you check BOL first... read up on the entry on date functions. But this should work:

SELECT CONVERT(VARCHAR, MONTH(mydate)) + '/' + CONVERT(VARCHAR, YEAR(mydate)) AS MonthAndYear, DATEPART(qq, mydate) AS Quarter

FROM mytable

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Different slant on the month/year...

SELECT RIGHT(CONVERT(CHAR(10),GETDATE(),103),7)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

By the way... these new columns should be "computed columns"... not real ones. Computed columns can be indexed if they are deteminate and they're self updating...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Nuts SSCrazy Points: 2547 More actions · Answer 4

Thanks for that Jeff

It works perfectly!!!

Keep up the good work

Cheers

wutang Old Hand Points: 389 More actions · Answer 5

wutang

Old Hand

Points: 389

July 23, 2007 at 3:28 pm

#721708

Jeff is SQL MVP

Nuts SSCrazy Points: 2547 More actions · Answer 6

Nuts

SSCrazy

Points: 2547

July 23, 2007 at 6:00 pm

#721726

Good to know that!!!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Thanks Wutang , but, nope... just a regular ol' SQL guy answering questions if I can.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 8

The modesty doesn't suite you

There are many people listen (read) to what you say (write)

N 56°04'39.16"
E 12°55'05.25"