Formatting YYYYMM to MMMYYYY in SSRS

Question

I would like to to format one of my data fields to be in the date format of MMM YYYY. For example, if "201209" is being returned, then I would like it to be displayed as Sep 2012. On the SQL side I am using a CAST to only look at the Year and Month, normally this field would look like "20120914". Here is what I used in the procedure:

cast(left(cast(TransactionDateKey as varchar(100)), 6) as int)

Within SSRS, I use the following code:

=Format(DateValue(MonthName(Right(Fields!Month.Value, 2))
 + "," + 
 Left(Fields!Month.Value,4)), "Y")

however the output is "September 2012", how would I go about get the abbreviation "Sep", instead of the full month name?

Answer 1

Another Option is Format() in SQL Server

Select Format(CONVERT (date,convert(char(8),TransactionDateKey )),'MMM yyyy')

For example

Select Format(CONVERT (date,convert(char(8),20120914 )),'MMM yyyy')
-- Returns Sep 2012

I should note that Format() is not known for its performance, but does offer some nice features