Formatting Dates [SSRS]

Date and TimeIn my previous article I discussed all possible methods to get numbers formatted any way you prefer.

But I didn’t mention a special type of number: date and time values!  So let’s look into these now.

The Format Property

Just as with regular numbers we can use the Format property of the SSRS textbox to tell the report what the output should look like.  But what do we need to type in there?  Well, once again there are some predefined date/time formats which can be used here.

The table below shows a couple of examples of how to format the output of the Now() function:

Formatting Now() through predefined formats and the Format property

And if those predefined formats are not sufficient for your requirements then, guess what, there are some user-defined formats which can be used instead.  Some examples:

Formatting Now() through user-defined formats and the Format property

The FormatDateTime Function

What if you don’t want or just can’t use the Format property?  No worries, in that case you can rely on a couple of functions.  The first one is the FormatDateTime function.

Here’s what it’s definition looks like:

Function FormatDateTime(
   ByVal Expression As DateTime,
   Optional ByVal NamedFormat As DateFormat = DateFormat.GeneralDate
) As String

The first parameter should be obvious, it’s the DateTime to be formatted to string.

The second parameter expects a value of the DateFormat enumeration.  Here are the possible settings and their corresponding output:

Using the FormatDateTime function

The Format Function

Alright, so what if that FormatDateTime function is still too limited?  Then rely on the Format function!  Some examples:

Using the Format function to format a datetime value

As you can see, all possible strings that you can put in the Format property will work here as well.  In fact, the links I gave you earlier lead to subpages of this Format function!

Impact of The Language Property

You should be aware that the exact output of the Date/Time formatters depends on the language settings of the report.  Each report has got a property called Language.  By default this is set to en-US.  Let’s set it to nl-BE (yes, Belgian Dutch) and look at some examples:

Impact of Language property

Impact of System Settings

You should also be aware that this Language property is not the only property that has an impact on date/time value rendering.  The system settings play their role too and have an impact on how the Date/Time report parameters behave.

These are my current settings, Format is set to Dutch (Belgium):

Region and Language settings

With those settings here’s what a Date/Time report parameter looks like in BIDS preview:

A Date/Time parameter with system settings set to Dutch (Belgium)

As you can see, the month and day names or abbreviations are translated to Dutch and the format of the parameter is dd/MM/yyyy.

Here’s what that same parameter looks like when I change my system settings to English (United States):

The Date/Time parameter with system settings set to English (United States)

Conclusion

The above should give you sufficient info on getting those dates and times formatted anyway you’d like.  If not, type your problem down in a comment below and I’ll see if I can help you!

Have fun!

Valentino.

Share

Tags: , ,

  1. Roy’s avatar

    Hello,

    I am trying to output date parameters used to produce a report, and am using this:
    =Format(Parameters!DATEFROM.Value, “dd/MM/yyyy”) & ” To ” & Format(Parameters!DATETO.Value, “dd/MM/yyyy”)
    which just gives #Error.
    Any suggestions please?
    Roy

    Reply

  2. Cad Delworth’s avatar

    Roy, if this is in a TextBox, add two Placeholders (one for each DateTime) to the text in the box, set the Expression property of each Placeholder to the correct Parameter.Value (e.g. =Parameters!DATEFROM.Value) and use the Placeholders’ Number property to format each date. Simples!

    Reply

© 2008-2017 BI: Beer Intelligence? All Rights Reserved