How to Format Date and Time on Ssrs Report

How do I format date and time on ssrs report?

=Format(Now(), "MM/dd/yyyy hh:mm tt")

Output:

04/12/2013 05:09 PM

Formatting date time in SSRS

There is another way than this two suggested.

Right click in your expression > Placeholder Properties > Number >
Date > Then pick your desired format.

SSRS Time Format

A somewhat quick and crude way of handling this is in an expression:

Format(DateAdd(DateInterval.Second, Fields!Seconds.Value, CDate("1970-01-01")), "hh:mm tt")

Breaking it down, it doesn't matter what date you choose since you are only concerned with the time value. You also don't need to set the time to midnight as it'll default to midnight.

The expression adds your seconds from midnight using DateAdd. The result of this would look something like:

1970-01-01 15:30:00

Finally, Format with the custom format string hh:mm tt will take the time part of the datetime value. tt specifies whether AM/PM is included in the final formatted output.

Formatting Datetime in SSRS Expression

SSRS doesn't have built-in support for ordinal numbers (i.e. "1st" or "2nd" instead of "1" or "2"). This page contains custom code to add this functionality to your SSRS report; however it is slightly wrong. Here is a corrected version:

Public Function FormatOrdinal(ByVal day As Integer) as String
        ' Starts a select case based on the odd/even of num
        if(day = 11 or day = 12 or day = 13)

        ' If the nymber is 11,12 or 13 .. we want to add a "th" NOT a "st", "nd" or "rd"
            return day.ToString() + "th"

        else
    ' Start a new select case for the rest of the numbers
    Select Case day Mod 10
        Case 1
            ' The number is either 1 or 21 .. add a "st"
            Return day.ToString() + "st"
        Case 2
            ' The number is either a 2 or 22 .. add a "nd"
            Return day.ToString() + "nd"
        Case 3
            ' The number is either a 3 or 33 .. add a "rd"
            Return day.ToString() + "rd"
        Case Else
             ' Otherwise for everything else add a "Th"
            Return day.ToString() + "th"
    End Select
        end if
End Function

If you add this code to the code section of your report under report properties, your textbox expression would be:

Code.FormatOrdinal(Day(Globals!ExecutionTime)) & " " & MonthName(Month(Globals!ExecutionTime), False) & " - " &  Code.FormatOrdinal(Day(DateAdd("d", -30,Globals!ExecutionTime))) & " " & MonthName(Month(DateAdd("d", -30,Globals!ExecutionTime)), False)



Changing the SSRS date picker format to DD/MM/YYYY

Unfortunately, the report server's location settings and the user's browser's location settings don't (by default) do anything to the report formats because the report has its own Language property that is set by default to en-US. So unless you modify this property, you are always going to get US formats. You access this property on the report's Property panel.

Now you could set it to en-UK but a better solution is to set it to the user's internationali[s|z]ation setting by using =User!Language. Now you can use d as the cell Format property for the user's regional short date format.

Handy formats that use the user's regional formats are:

• d = short date format
• N2 = number with 2 decimal places
• N0 = number with no decimal places
• P0 = percent with no decimal places

---

Related Topics