Format Month Number to Month Name–Multiple Ways with T-SQL



if object_id('dbo.test','U') is not null
Drop table dbo.test
CREATE TABLE dbo.test([ID] INT,Date6 char(6)
)

 INSERT INTO test VALUES (1,'201401'),
 (2,'201402'),
 (3,'201403'),
 (4,'201404'),
 (5,'201405'),
 (6,'201406'),
 (7,'201407'),
 (8,'201408'),
 (9,'201409'),
 (10,'201410'),
 (11,'201411'),
 (12,'201412');
GO

--Option  1
select Left(DateName(month,Dateadd(month,right(Date6,2)-1,0)),3)+'-'+Left(Date6,4)
FROM test
--Option 2
SELECT replace(Right( CONVERT(VARCHAR(11),CONVERT(DATE,Date6+'01'),106),8),' ','-') FROM test
--Option 3
SELECT Cast(DATENAME(MONTH,right(Date6,2)*28) as varchar(3)) +'-'+Left(Date6,4)  FROM test
--Option 4
SELECT FORMAT(Cast(Date6+'01' as date),'MMM-yyyy') As [Mon-Year]
FROM test
--Option 5
select Left(DateName(MONTH,Dateadd(month, right(Date6,2) - 1, 0)),3)+'-'+Left(Date6,4)
FROM test
--Option 6
SELECT LEFT(DATENAME(MM,CONVERT(DATE,Date6+'01')),3) + '-'+ Left(Date6,4) As [Mon-Year]
FROM test

drop table test


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s