Month name and year in sql

Question

Post reply

Month name and year in sql

pwalter83

SSChampion

Points: 14575
More actions
May 12, 2015 at 5:00 am

#317318

Hi,
Can anyone please advice on how to convert a date to the format as Month name and year(2 digits).
For e.g.- Jan 14, Feb 14......Mar 15.
Thanks.

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

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

BrainDonor SSCoach Points: 19254 More actions · Answer 1

SELECT LEFT(DATENAME(MONTH,GETDATE()),3) + ' ' + RIGHT('00' + CAST(YEAR(GETDATE()) AS VARCHAR),2)

Steve Hall
Linkedin
Blog Site

pwalter83 SSChampion Points: 14575 More actions · Answer 2

BrainDonor (5/12/2015)
SELECT LEFT(DATENAME(MONTH,GETDATE()),3) + ' ' + RIGHT('00' + CAST(YEAR(GETDATE()) AS VARCHAR),2)

Thanks very much it worked perfectly !

Just one more thing if you can help me with, I need to sort the same now in order of calendar months as below (which needs to be further displayed as bar graphs):

Apr 14

May 14

Jun 14

July 14

Aug 14

Sep 14

Oct 14

Nov 14

Dec 14

Jan 15

Feb 15

Mar 15

Apr 15

Could you please advice on how to achieve this ?

Thanks again.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

You already have your date column that you're formatting for display, so just sort by the date column itself and not the output.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

I would suggest putting that type of formatting in your bar graph instead of in your sql. Formatting in the front end is best so you can maintain your dates so that doing things like sorting is easy.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

I agree with Sean, formatting in the front end will help you with order and more format functions.

However, here's a shorter way to do it.

SELECT STUFF( CONVERT( char(11), GETDATE(), 0), 5, 5, '')

,STUFF( CONVERT( char(11), GETDATE()), 5, 5, '')

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

There is a way to seemingly sort formatted month and year combinations. Please see the following article and apply your favorite format.

http://www.sqlservercentral.com/articles/T-SQL/71511/

I second what the others have stated, though. If there's a GUI or reporting tool (including spreadsheets) involved, formatting dates and times in T-SQL is one of the worst things you can do because it overrides the local date and time format settings.

--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)

BrainDonor SSCoach Points: 19254 More actions · Answer 7

Luis Cazares (5/12/2015)
SELECT STUFF( CONVERT( char(11), GETDATE(), 0), 5, 5, '')
,STUFF( CONVERT( char(11), GETDATE()), 5, 5, '')

It took me a few seconds to understand that one Luis, but I like it - far more elegant than mine.

Steve Hall
Linkedin
Blog Site