Change date&time format in date picker SSRS

Question

how i can change format in date and time picker for Reporting services. Currently is always in format dd/MM/yyyy, system date&time format is the same. I want to change date to be in format MM/dd/yyyy

Answer 1

Necromancing.
Yes, you can actually do that - sort of.
First, notice that SSRS takes the date format from the language that is specified in your browser.

So you could just change the language of your browser.
Obviously, you don't wanna tell each and every of your users to do that (if they have the rights & skills to do so in the first place).

So you pass an additional parameter into your report:
I called it in_sprache (Sprache means language in German, with possible values "DE, "FR", "IT", "EN").

Now you need to change the localization process, by overriding the virtual method "InitializeCulture" in ReportViewer.aspx.

You can find ReportViewer in

C:\Program Files\Microsoft SQL Server\MSRS<Version>.MSSQLSERVER
C:\Program Files\Microsoft SQL Server\MSRS<Version>.<Instance>

e.g.

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER

There you add (in the source-code of the /ReportServer/Pages/ReportViewer.aspx):

<script type="text/C#" runat="server">

protected override void InitializeCulture()
{
    string sprache = System.Web.HttpContext.Current.Request.QueryString["in_sprache"];

    if(string.IsNullOrEmpty(sprache))
        sprache = "";

    switch(sprache.ToLowerInvariant())
    {
        case "de":
            sprache = "de-CH";
            break;
        case "fr":
            sprache = "fr-CH";
            break;
        case "it":
            sprache = "it-CH";
            break;
        case "en":
            sprache = "en-US";
            break;
        default:
            sprache = "";
            break;
    }

    // System.Web.HttpContext.Current.Response.Write(sprache);
    if(!String.IsNullOrEmpty(sprache))
    {
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture(sprache);
        System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo(sprache);
    }

    base.InitializeCulture();
}

</script>

This will override how ASP.NET localises, taking the value of the url-parameter in_sprache (in_sprache must be a parameter of your report) instead of the browser-user-language.

Now, unfortunately, you must also override context.Request.UserLanguages for the datepicker to work properly... (otherwise you get an error if culture is en-US and day > 12)
you can do so only by adding a HTTP-Module (libRequestLanguageChanger.dll)
into the web.config of ReportServer

  <system.web>
    [...]
    <httpModules>
      [...]
      <add name="RequestLanguageChanger" type="libRequestLanguageChanger.RequestLanguageChanger, libRequestLanguageChanger" />

    </httpModules>
    [...]
  </system.web>

. (Requires changing trust-level from rosetta to "Full", unless you can figure out how to change the rosetta-policy to allow this http-module).

Since we can also override InitializeCulture in the HTTP-Module, you don't really have to add the runat="server" script to ReportViewer.aspx.

namespace libRequestLanguageChanger
{


    public class RequestLanguageChanger : System.Web.IHttpModule
    {


        void System.Web.IHttpModule.Dispose()
        {
            // throw new NotImplementedException();
        }


        void System.Web.IHttpModule.Init(System.Web.HttpApplication context)
        {
            // https://stackoverflow.com/questions/441421/httpmodule-event-execution-order
            context.BeginRequest += new System.EventHandler(context_BeginRequest);
        }


        void context_BeginRequest(object sender, System.EventArgs e)
        {
            System.Web.HttpApplication application = sender as System.Web.HttpApplication;
            System.Web.HttpContext context = application.Context;

            if (context.Request != null)
            {
                // string language = context.Request.Headers["Accept-Language"];
                string language = null;
                // string url = context.Request.RawUrl;
                // string referrer = null;


                if (context.Request.UrlReferrer != null)
                {
                    // referrer = context.Request.UrlReferrer.OriginalString;

                    string queryString = context.Request.UrlReferrer.Query;
                    System.Collections.Specialized.NameValueCollection queryStrings = System.Web.HttpUtility.ParseQueryString(queryString);
                    language = queryStrings["in_sprache"];
                }

                if(context.Request.QueryString["in_sprache"] != null)
                    language = context.Request.QueryString["in_sprache"];

                if (!string.IsNullOrEmpty(language))
                {
                    language = language.ToLowerInvariant();

                    switch (language)
                    {
                        case "de":
                            language = "de-CH";
                            break;
                        case "fr":
                            language = "fr-CH";
                            break;
                        case "it":
                            language = "it-CH";
                            break;
                        case "en":
                            language = "en-US";
                            break;
                        default:
                            language = "";
                            break;
                    }

                } // End if (!string.IsNullOrEmpty(sprache)) 

                // SQL.Log(url, referrer, sprache);


                // Simulate Browser-Language = in_sprache 
                if (!string.IsNullOrEmpty(language))
                {
                    // context.Request.Headers["Accept-Language"] = language;

                    System.Globalization.CultureInfo culture = new System.Globalization.CultureInfo(language);
                    System.Threading.Thread.CurrentThread.CurrentCulture = culture;
                    System.Threading.Thread.CurrentThread.CurrentUICulture = culture;

                    if (context.Request.UserLanguages != null)
                    {

                        // System.Globalization.CultureInfo culture = new System.Globalization.CultureInfo(context.Request.UserLanguages[0]); 
                        for (int i = 0; i < context.Request.UserLanguages.Length; ++i)
                        {
                            // context.Request.UserLanguages[i] = "en-US";
                            context.Request.UserLanguages[i] = language;
                        } // Next i 

                    } // End if (context.Request.UserLanguages != null)

                } // End if (!string.IsNullOrEmpty(language)) 

            } // End if (context.Request != null) 


        } // End Sub context_BeginRequest 


    } // End Class 


} // End Namespace 

And there you are, ReportServer with a "custom"-culture date-format, without having to tell the user to change the browser-language.

You can fetch the links from a SQL-table, where you can replace the text {@language} with the culture-name of your user (in my case DE, FR, IT, EN, since those are the languages spoken in Switzerland).

SELECT 
    REPLACE(RE_Link, '{@language}', T_Users.USR_Language) 
FROM T_Reports 
LEFT JOIN T_Users ON USR_ID = @usr 


-- http://localhost/Reportserver?/SomeCompany/SomeReport&rs:Command=Render&in_sprache=de
-- http://localhost/Reportserver?/SomeCompany/SomeReport&rs:Command=Render&in_sprache=fr
-- http://localhost/Reportserver?/SomeCompany/SomeReport&rs:Command=Render&in_sprache=it
-- http://localhost/Reportserver?/SomeCompany/SomeReport&rs:Command=Render&in_sprache=en

Answer 2

In your designer, the date format is determined by the culture of the operating system.

Once deployed, the date format is determined by language of the browser.