Home > SSRS Expert > SSRS #67 – Custom Date and Time Format Strings

SSRS #67 – Custom Date and Time Format Strings

When it comes to using the correct format strings for Date and Time in SSRS, many Reporting Services developers wonder where to find the valid custom Date and Time styles (or strings).

I have several SSRS books. They all give some expression examples on how to format Date and Time, but none of them mentioned where we can find all the valid Date and Time format styles (or strings).

Custom Date and Time Format Strings for VB.NET or C#.NET code

Because scripting in Reporting Services uses VB.NET or C#.NET code, so the right place to look for those format strings is VB.NET or C#.NET documentations.

Custom Date and Time Format Strings

The above link is to the Microsoft MSDN library for .NET Framework 4. It has all the mystery strings we are looking for.

Here is a part from the above documentation showing upper case M and lower case m. Upper case M is clearly reserved for month, and lower case m is reserved for minute.

image

Here is the part that shows lower case t is used for AM/PM designator.

image

Two Examples of Custom Date and Time Formatting

This example from one of my SSRS reports uses the Format() function to format the Time portion of a DateTime field. The first portion is simply formatting the time, and the second portion adds 30 minutes first then formats it.

image

 

Here is the result.

image

The second example is a little different from the first example. It comes from a SSRS report that uses MDX query against an Analysis Services data source. It takes a DateTime user input value, and format it, and then convert it to a valid MDX member value for a Date dimension.

image

CDate() function is used first to convert the parameter to Date data type. Format() function then formats the value to the date format that is the same as the format in the Date dimension. Then I prefix the appropriate level names and dimension name (the dimension’s name is actually Time). Now the result will be a perfectly acceptable MDX member value.

image

Here is the result.

image

With the above link to the custom Date and Time format strings, now we have no excuses but to say yes to our users when it comes to their wild Date and Time formatting requirements. Just remember, the format strings are case sensitive. As a matter of fact, the entire Reporting Services environment is case sensitive.

Have fun with formatting!

Categories: SSRS Expert
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: