how to seperate the month and year from date?

Question

Post reply

how to seperate the month and year from date?

raghuldrag

Hall of Fame

Points: 3160
More actions
May 16, 2014 at 6:17 am

#288924

Hi Friends,
i ve the date part like '2014-04-01' when i show output like 'apr-14'
how to make that?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

DECLARE @dt DATETIME;

SET @dt = GETDATE();

SELECT DATENAME(month,@dt), YEAR(@dt)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Neil Burton SSC-Insane Points: 23008 More actions · Answer 2

Borrowing heavily from Gail to give the results in the same column with a two digit year

DECLARE @dt DATETIME;

SET @dt = GETDATE();

SELECT DATENAME(month,@dt)+'-'+ right(YEAR(@dt),2)

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

David Burrows SSC Guru Points: 65143 More actions · Answer 3

STUFF(STUFF(CONVERT(varchar(9),[date],6),1,3,''),4,1,'-')

Far away is close at hand in the images of elsewhere.
Anon.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

Taking it one step further...

DECLARE @dt DATETIME;

SET @dt = '2014-04-01';

SELECT left(DATENAME(month,@dt),3)+'-'+ right(YEAR(@dt),2)

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001