I'm creating a report in SSRS and across the top I have a header with a placeholder for "Last Refreshed" which will show when the report last ran.
My function in the placeholder is simply this:
=Format(Now, "dddd dd MMMM yyyy hh:mm tt")
Which looks like this:
Monday 22 September 2015 09:46 AM
I want to format the day value with the English suffix of st, nd, rd and th appropriately.
I can't find a built in function for this and the guides I've looked at so far seem to describe doing it on the SQL side with stored procedures which I don't want. I'm looking for a report side solution.
I thought I could get away with an ugly nested IIF that did it but it errors out despite not giving me any syntax errors (whitespace is just for readability).
=Format(Now, "dddd " +
IIF(DAY(Now) = "1", "1st",
IIF(DAY(Now) = "21","21st",
IIF(DAY(Now) = "31","31st",
IIF(DAY(Now) = "2","2nd",
IIF(DAY(Now) = "22","22nd",
IIF(DAY(Now) = "3","3rd",
IIF(DAY(Now) = "23","23rd",
DAY(Now) + "th")))))))
+ " MMMM yyyy hh:mm tt")
In any other language I would have nailed this ages ago, but SSRS is new to me and so I'm not sure about how to do even simple string manipulation. Frustrating!
Thanks for any help or pointers you can give me.
Edit: I've read about inserting VB code into the report which would solve my problem, but I must be going nuts because I can't see where to add it. The guides say to go into the Properties > Code section but I can't see that.
Go to layout view. Select Report Properties.Click on the "Code" tab and Enter this code
Public Function ConvertDate(ByVal mydate As DateTime) as string
Dim myday as integer
Dim strsuff As String
Dim mynewdate As String
'Default to th
strsuff = "th"
myday = DatePart("d", mydate)
If myday = 1 Or myday = 21 Or myday = 31 Then strsuff = "st"
If myday = 2 Or myday = 22 Then strsuff = "nd"
If myday = 3 Or myday = 23 Then strsuff = "rd"
mynewdate = CStr(DatePart("d", mydate)) + strsuff + " " + CStr(MonthName(DatePart("m", mydate))) + " " + CStr(DatePart("yyyy", mydate))
return mynewdate
End function
Add the following expression in the required field. I've used a parameter, but you might be referencing a data field?
=code.ConvertDate(Parameters!Date.Value)