I want Datetime Parameter in DDMMYYYY Format in ssrs report

Question

I want to change the Datetime parameter to DDMMYYYY format.its default value is MMddYYYY.when user will select date from date picker the date should be display in ddmmyyyy format. I tried all expression like

=Format(Parameters!DateFrom.Value, "dd/MM/yy")
=cdate(format(DateAdd("d", -61, now),"dd/MM/yyyy"))
=cdate(format(DateAdd("d", -61, now),"dd/MM/yyyy"))

but all doesnt work.

Answer 1

Necromancing.
Yes, you can - 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 the browser. Obviously, you don't wanna tell your users to do that.

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 the browser-user-language with the one specified in the url-parameter in_sprache (in_sprache must be a parameter of your report).

Now, you must also override context.request.userLanguages for the datepicker to work properly... 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.