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.

Hyoscyamus

Please try to help me to solve this problem. client is ordering me to have that format – Hyoscyamus

I don't think you can specify the format of a date picker parameter. I think it will automatically use the default format that is set at the server level. – Adaptable

yea right Tab but i want to change that default date time parameter in ddmmyyyy format. – Hyoscyamus

Actually it looks like it can maybe be done at the report level. Try the steps in this article: msdn.microsoft.com/en-us/library/… – Adaptable

Thanks Tab for the link but i want to apply it for datetime parameter not for the textbox. – Hyoscyamus

That's what I'm telling you; I don't think you can apply it to just a parameter. I think you have to set the locale of the entire report in order to affect the parameters. – Adaptable

How about this one? =CDate(format(now,"yyyy-MM-dd")) see this post biatlink.wordpress.com/2013/08/05/… – Painting

and this one too thavash.wordpress.com/2007/04/10/… – Painting

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://mcmap.net/q/182768/-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.

Fidget

This is brilliant! Reminds me that I ended up writing a whole custom front end for SSRS to get more control. See https://mcmap.net/q/861824/-alternative-ssrs-front-ends-closed – Orlov

@StefanSteiger would you be able to remember, by any chance, what steps you took to figure out this solution? I'm trying to figure out an equivalent solution for PowerBI reports (which are also hosted in SSRS but appear to ignore the language header). – Sorosis

@user5226582: First, I thought it goes for navigator.language/systemLanguage by JavaScript. so I changed those, but that didn't work. Then I overwrote the the culture in the aspx. That worked for the dropdown boxes' text. Then i noticed it doesn't work with the datepicker. So I figured it must be a handler, and uses the HTTP-header - and the only way to override that was to create a http-module. So I overwrote the header with a http-module. That didn't work. So I figured it must create request.language before header is changed, and changed request.language instead. Surprisingly, that worked. – Fidget

@user5226582: Maybe PowerBI takes the JavaScript values (navigator.language & navigator.systemLanguage). Perhaps you have to overwrite these. – Fidget

@StefanSteiger Amazing, appreciate the response! – Sorosis

Answer 2

You can't. The closest I can think of is to set an expression on the language property of the report to =User!Language, this will at least display in the users system date format.

Holdfast

thanks energ1ser, can you please tell me where i can apply this expression. – Hyoscyamus

please refer to the image I have added. – Holdfast

After Changing language also the same issue persist, date is not coming in DDMMYYYY format in datetime parameter. – Hyoscyamus

Answer 3

You dont even need to set the language to user!Language. SSRS will pick up the DateFormat from the local users regional settings.

But obviously you dont have a lot of control over this and if your customer is screaming for this format, they may not have their PC set with these settings?

good luck!

This report belows' language is set to en-au (which is dd/mm/yyyy)

Vermiculate

Thanks Trubs for your Reply but this 1 is not working, i followed your steps but still same issue exist. – Hyoscyamus

@trubs: And you will tell every user to do this, and tell the administrator to give the users that right. LoL. An answer like that reflects badly on your mental state. – Fidget

Answer 4

=Format(CDate(Parameters!DateFrom.Value), "yyyy-MM-dd HH:mm:ss")

Lancewood

Answer 5

Following on from the suggestion by Trubs, I set the language in the browser (Chrome this time) and it formatted the values correctly. I changed language from English (US), which uses the mm/dd/yy format to English (Australia), which uses the dd/mm/yy format. Certainly we couldn't have every user changing their browser setting but DevOps could and should definitely set the language for all browsers in their organization to the correct language for their country I would guess?

Incombustible