Convert YYYYMM to MMMYY

Question

I have a period 201604 (nvarchar). Is there a way that I can convert 201604 to APR16?

Answer 1

Use the DATENAME & SUBSTRING functions, like this:

declare @str nvarchar(50) = '201604'  select UPPER(left(datename(mm,cast(@str+'01' as date)),3))+substring(@str,3,2) --APR16 

Answer 2

It is a bit ugly, but you can't use any of the built-in date formatting stuff as is. Feel free to swap out the case statement for a join if you have a month names table, etc.:

DECLARE @exampleVal NVARCHAR(6) = '201604';  SELECT CASE SUBSTRING(@exampleVal, 5, 2)          WHEN '01' THEN 'JAN'          WHEN '02' THEN 'FEB'          WHEN '03' THEN 'MAR'          WHEN '04' THEN 'APR'          WHEN '05' THEN 'MAY'          WHEN '06' THEN 'JUN'          WHEN '07' THEN 'JUL'          WHEN '08' THEN 'AUG'          WHEN '09' THEN 'SEP'          WHEN '10' THEN 'OCT'          WHEN '11' THEN 'NOV'          WHEN '12' THEN 'DEC'        END +        SUBSTRING(@exampleVal, 3, 2)