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

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


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s