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?

If at all possible you should change your database so it is a datetime datatype instead of an int or varchar which is what it sounds like you have. This isn't always possible so what you really should do is pass a date(or datetime) datatype to your report. Then do the formatting based on the date datatype. — Sean Lange, Jan 23 '17 at 21:26
@Sean Lange -- very true; just remember, in many organizations, changing a database can be an extremely daunting task! — JosephDoggie, Mar 01 '19 at 16:21
@JosephDoggie hence this part. "This isn't always possible". :) — Sean Lange, Mar 01 '19 at 16:24

John Cappelletti · Accepted Answer · 2017-01-24T15:31:43.467

2

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

edited Jan 24 '17 at 15:31

answered Jan 23 '17 at 21:17

John Cappelletti

79,615
7
44
66

1

Thanks for getting back with me, however when I get rid of my casting statement and replace it with you suggestion, MMM yyyy is returned instead of a value – Istaley Jan 24 '17 at 14:47
2

@Istaley Two questions. 1) is TransactionDateKey a date or datetime field? 2) Are you running the Format() in SQL Server (not SSRS) – John Cappelletti Jan 24 '17 at 14:57
1

1) TransactionDateKey in our fact table is an int. I casted it above to cut off the day just to leave YYYYMM. I am making a matrix report and we want to know the amount of fills a person has for each month, not each day. Maybe there is a better way than to do a cast, but I am not sure. 2) Yes, I tried Format() is SQL Server – Istaley Jan 24 '17 at 15:20
2

@Istaley Then you simply have to cast the int (yyyymmdd) as a date. So try Select Format(cast(TransactionDateKey as date),'MMM yyyy') – John Cappelletti Jan 24 '17 at 15:26
1

I get the following error in SSMS "Explicit conversion from data type int to date is not allowed" – Istaley Jan 24 '17 at 15:32
1

@Istaley with the convert as listed above? – John Cappelletti Jan 24 '17 at 15:33
1

@Istaley Never understand why folks store dates as INTs. You will always have to perform conversions – John Cappelletti Jan 24 '17 at 15:34
1

I apologize John, I didn't get the alert of your update and I tried the cast statement you gave me. The one at the very top worked!! Thanks so much! I can't anything about the int thing, I am just a foot-soldier, but I do agree with you. – Istaley Jan 24 '17 at 15:39
1

@Istaley No apologies. I figured you inherited it. Trust me, I've inherited far worse. cheers. – John Cappelletti Jan 24 '17 at 15:41

score 2 · Answer 2 · answered Jan 24 '17 at 14:19

If you are returning an actual datetime field in your dataset - which is what your TransactionDateKey appears to be - you can handle the formatting completely in an SSRS expression using format:

=format(Fields!TransactionDateKey.Value,"MMM yyyy")

If you are returning your yyyyMM for grouping purposes, there is nothing stopping you grouping on a datetime value of the first of the month for TransactionDateKey within your SQL:

select dateadd(m,datediff(m,0,TransactionDateKey),0) as FirstDayOfTheMonth

If you absolutely need to return a varchar in the format yyyyMM, you can convert it to MMM yyyy, though you first need to convert it to a date in your expression - slashes and all - before SSRS will play ball:

=format(cdate(left(Fields!Month.Value,4) & "/" & right(Fields!Month.Value,2) & "/01"), "MMM yyyy")

score 1 · Answer 3 · answered Jan 23 '17 at 21:30

1

In SSRS you can use:

=StrConv(LEFT(
MONTHNAME(REPLACE(RIGHT(Fields!Month.Value,2),"0","")),3
),vbProperCase,NOTHING) & " " & LEFT(Fields!Month.Value,4)

Which returns Sep 2012 for 201209.

Let me know if this helps.

answered Jan 23 '17 at 21:30

alejandro zuleta

13,962
3
28
48

Formatting YYYYMM to MMMYYYY in SSRS

3 Answers3