Format datetime day with st, nd, rd, th

Question

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.

Naresh

Answer 1

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)

Variegated

Your answer definitely looks like it will do the trick, but I feel like I'm being a total moron here because for the life of me I can't find the place to add the code. I'm wondering if in my ignorance of SQL reporting I've used the wrong terminology somewhere which is confusing us both. I opened "SQL Server Business Intelligence Development Studio" and created a new project, added a report which is an RDL file and now I'm in design view. If I go to the properties for the report it just has it's path, the properties for the project doesn't have a code tab, and the code view looks like HTML. – Naresh

"Report" is in the tool bar above the main Design window. This sometimes disappears, so click a few times on your design if you cannot see it! We all have to learn at some time! – Variegated

OK, I still feel stupid, but at least it's working now. Thanks Miguel, you're a genius ;-) – Naresh

Answer 2

I found an easy way to do it. Please see example below;

= DAY(Globals!ExecutionTime) & 
    SWITCH(
    DAY(Globals!ExecutionTime)= 1 OR DAY(Globals!ExecutionTime) = 21 OR DAY(Globals!ExecutionTime)=31, "st",
    DAY(Globals!ExecutionTime)= 2 OR DAY(Globals!ExecutionTime) = 22 , "nd",
    DAY(Globals!ExecutionTime)= 3 OR DAY(Globals!ExecutionTime) = 23 , "rd",
    true, "th"
    )

Smolt

Answer 3

Right Click on the Textbox, Go To Textbox Properties then, Click on Number tab, click on custom format option then click on fx button in black.

Write just one line of code will do your work in simpler way:

A form will open, copy the below text and paste there to need to change following text with your database date field.

Fields!FieldName.Value, "Dataset"

1. Replace FieldName with your Date Field

2. Replace Dataset with your Dateset Name

   ="d" + switch(int(Day((Fields!FieldName.Value, "Dataset"))) mod 
   10=1,"'st'",int(Day((Fields!FieldName.Value, "Dataset"))) mod 10 = 
   2,"'nd'",int(Day((Fields!FieldName.Value, "Dataset"))) mod 10 = 
   3,"'rd'",true,"'th'") + " MMMM, yyyy"
   

Issykkul

To suggest an improvement you are missing the case for values between the 11th and 19th – Note

Answer 4

you can use this directly replacing 'dbo.Getlocaldatetime()' with your column name

(SELECT FORMAT(dbo.Getlocaldatetime(), 'dddd, d') + 
                CASE WHEN DAY(dbo.Getlocaldatetime()) % 10 = 1 AND DAY(dbo.Getlocaldatetime()) != 11 THEN 'st'
                WHEN DAY(dbo.Getlocaldatetime()) % 10 = 2 AND DAY(dbo.Getlocaldatetime()) != 12 THEN 'nd'
                WHEN DAY(dbo.Getlocaldatetime()) % 10 = 3 AND DAY(dbo.Getlocaldatetime()) != 13 THEN 'rd'
                ELSE 'th' END + 
                FORMAT(dbo.Getlocaldatetime(), ' MMMM') ) AS [SystemDate]

Etrem