Convert YYYYMM to MMMYY

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)

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