0

Just wondering if anyone can help me format a Year/Month INT column in the format YYYYMM to a user friendly date with short month e.g.Jan, Feb rather than a number.

A couple of examples:

201101 to Jan 2011
201312 to Dec 2013

I can substring out the components no worries, I just can't work out how to turn 01 to Jan.

EDIT:

Based on @Karl's answer this it what I have come up - it's pretty ugly but it works:

DECLARE @Test INT
SET @Test = 201206

SELECT SUBSTRING(DATENAME(month, DATEADD(month, CAST(SUBSTRING(CAST(@TEST AS VARCHAR), 5, 2) AS INT) - 1 , CAST('2008-01-01' AS datetime))),1,3) + ' ' + SUBSTRING(CAST(@Test AS VARCHAR),1,4) as Test
Rob
  • 10,004
  • 5
  • 61
  • 91
  • `SELECT LEFT(DATENAME(m, [Value] % 100), 3) + STR([Value] / 100, 5)` – Oleks Apr 09 '12 at 09:30
  • @alex - that doesn't work sorry - SELECT LEFT(DATENAME(m, 201206 % 100), 3) + STR(201206 / 100, 5) produces - Jan 2012... it should be Jun 2012 – Rob Apr 09 '12 at 09:44
  • Sorry, should be `SELECT LEFT(DATENAME(m, DATEADD(m, 201206 % 100, -1)), 3) + STR(201206 / 100, 5) ` – Oleks Apr 09 '12 at 09:53

1 Answers1

0

Just another approach (drop the day part after conversion):

declare @test int;

set @test=201212

select substring(CONVERT(varchar(11),CONVERT(date, cast (@test * 100 + 1 as varchar(8)), 112),106),4,8)
Lord Peter
  • 3,433
  • 2
  • 33
  • 33