locked
SSRS Datetime Parameter value should display in DD/MM/YYYY format RRS feed

  • Question

  • Hi All

    we have 2 Datetime parameters in my report , where the value is shouwing in MM/DD/YYYY format . i have modified the language type of report to Fr-BE but there is not change . Users want to see the date value in DD/MM/YYYY format . how can we achieve this .


    Surendra Thota

    Tuesday, May 29, 2012 7:30 AM

All replies

  • Format(now,"dd/MM/yyyy") does it work?

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, May 29, 2012 7:33 AM
  • Hi Dimant

    i tried below formula in default value in datetime parameter

    =format(DateAdd("d", -61, now),"dd/MM/yyyy")

    but it is throwing error : The property ' DefaultValue' of report parameter ' StartDate' doesnt have the expected type .

    but when we given DateAdd("d", -61, now) it is not throwing any error .


    Surendra Thota

    Tuesday, May 29, 2012 7:53 AM
  • Hi There

    please put something like this

    =cdate(format(DateAdd("d", -61, now),"dd/MM/yyyy"))

    I hope this will help

    If you have any questions please do ask.

    Many Thanks
    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    • Proposed as answer by Avanish Tomar Monday, September 5, 2016 7:36 AM
    • Unproposed as answer by Avanish Tomar Monday, September 5, 2016 7:36 AM
    Tuesday, May 29, 2012 8:13 AM
  • Hi Syed

    its trowing error : Conversion from string "29/03/2012" to type 'Date' is not valid .


    Surendra Thota

    Tuesday, May 29, 2012 8:45 AM
  • Hi There

    Please have a look the screenshot. It is working for me.

    Many Thanks
    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    =cdate(format(DateAdd("d", -61, now),"dd/MM/yyyy"))

    • Proposed as answer by jdfogarty9 Tuesday, August 6, 2013 4:36 AM
    Tuesday, May 29, 2012 8:55 AM
  • Hi There

    Please have a look the screenshot. It is working for me.

    Many Thanks
    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    =cdate(format(DateAdd("d", -61, now),"dd/MM/yyyy"))

    Tuesday, May 29, 2012 8:55 AM
  • Hi Syed

    i did the same way , but still i am having the same issue .what datatype you took for parameter . i took date/Time


    Surendra Thota

    Tuesday, May 29, 2012 10:39 AM
  • Hi Surendra

     The calendar control will only be appear if you data type is date/time in SSRS parameter. If you please have a look on the above screenshot you can see calendar control

    Many Thanks

     Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

    .
    Tuesday, May 29, 2012 10:43 AM
  • Hi Surendra

     The calendar control will only be appear if you data type is date/time in SSRS parameter. If you please have a look on the above screenshot you can see calendar control

    Many Thanks

     Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

    .
     

    Hi  Surendra

    Which version of SSRS you are in. I am checking it inside SSRS 2008 r2

    Many Thanks

    Syed Qazafi Anjum


    Tuesday, May 29, 2012 10:58 AM
  • i am also using SQL server 2008 R2


    Surendra Thota

    Tuesday, May 29, 2012 11:04 AM
  • Hi Syed

    i have tested on my colluages system  it is working fine . it is not working in my system . i hope some system settings needs to be there .


    Surendra Thota

    Tuesday, May 29, 2012 1:04 PM
  • Hi Syed

    if i deploy in reporting server it is working fine without error . even normal  expression    =DateAdd("d", -61, Today()) is giving in that format only .


    Surendra Thota

    Tuesday, May 29, 2012 1:37 PM
  • Hi Surendra,

    The date format in Preview mode in Report Designer is determined by the culture of the operating system, while the date format in the report manager depends on the culture of the browser.

    Based on my tests, in report designer, the date format displayed in the parameter textbox does not strictly follow the culture setting. From the screenshot below, we can see that when the system language is set to en-GB and the date format set to "dd/MM/yyyy", the "Today:" value has the correct format, however, the date in the prompt box still keeps the previous format. It seems that this behavior is by design.

    In the report manager, the date format in the prompt box displays correctly when we change the browser language. To change the culture of Internet Explorer (IE), you can refer to these steps:

    1. Open IE, click Tools->Internet Options.
    2. In the “General” tab, click the button “Language".
    3. Add the required language to the list, and move the language to top.

    In this issue, since you want the users to see the required format, it doesn't matter how the parameter date displays in the report designer. You can apply the IE language settings to all clients. To do this, we can use the Group Policy to publish the settings. 

    Hope this helps.

    Regards,
    Mike Yin

    • Proposed as answer by Syed Qazafi Anjum Sunday, June 3, 2012 9:26 AM
    • Marked as answer by Mike Yin Wednesday, June 6, 2012 4:12 PM
    • Unmarked as answer by Mike Yin Thursday, November 14, 2013 4:47 AM
    Sunday, June 3, 2012 8:56 AM
  • Its not working for me...

    when i uses an expression  for my date/time (instead of none) the calender cannot be clicked and the text box next to the calendar is inaccessible either!! how!!

    Thursday, June 7, 2012 10:20 AM
  • Which expression you are using ?

    Surendra Thota

    Thursday, June 7, 2012 10:28 AM
  • Hi Kellyshl,

    Thanks for your posting.

    Could you post a screenshot to show us the current issue? The screenshot below is for your reference:

    Regards,
    Mike Yin 

     

    • Proposed as answer by pl80 Wednesday, March 15, 2017 4:54 PM
    Thursday, June 7, 2012 10:34 AM
  • So in what way is this issue solved? - the browser language setting has no influence on the date format in the parameter date picker box.

    Mike Yin - "It seems that this behavior is by design." - Is that the best Microsoft can do?

    My client is complaining about this - should I open a Microsoft Support incident for this?

    Microsoft's lack of understanding about the how the rest of the world operates astounds me.

    J.


    James Boman - http://www.boman.biz Software Consultant for IPMO - http://www.ipmo.com.au

    Tuesday, September 4, 2012 6:49 AM
  • I agree - I've been fking around with this peice of sh*t for over 8 hours today.

    The report viewer date function reverts back US even though EVERYTHING ois set to UK English.

    I Have even applied SP1 to SQL2012 & that new bullSh*t patch (http://support.microsoft.com/kb/2764343) that is supposed to fix this. Guess what neither work!

    • Proposed as answer by d4vinder [MVP] Tuesday, January 22, 2013 4:21 PM
    Tuesday, January 22, 2013 4:20 PM
  • Has anyone found a fix for this?

    I have a SharePoint 2010 server we are setting up with SQL Report Server running on SQL 2012. Everything is working fine, but when you put in a date parameter, then click apply the date format reverts to US based (MM/DD/YYYY) after displaying the report. Before clicking it displays in UK format (DD/MM/YYYY) and also running the report in the SQL Report Builder itself, it stays as UK.

    I've checked both SharePoint and SQL server and they are all UK region at the OS level. I have changed the SQL server language to UK. When running SELECT @@Language, @@Langid it shows British 23. All login users are set to British.

    I have followed various online suggestions and still whenever I click apply in the report on SharePoint the date changes. I have no idea where or why the date format is changing?

    Tuesday, March 26, 2013 9:02 AM
  • Hi Lidhelp

    Did you changed the Report Laguage to en-GB ?


    Surendra Thota

    Tuesday, March 26, 2013 11:36 AM
  • Yes, the report language is GB
    Tuesday, March 26, 2013 12:35 PM
  • I have this same issue on my new laptop (suffering with it for about 9 months actually).  I used to work around it by launching Visual Studio as a different user.  That doesn't work anymore as I can't get the report preview to display when run as another user (apparently that's an issue reported in other forum threads).

    I lodged something on Connect about this quite a while ago and the op there was also quite unhelpful.  Using expressions and so on is NOT an option.  It's impossible to preview a report using date pickers now as it seems the control validates using one format and then the engine seems to immediately validate using the other.  I suppose an ambiguous date like 3/2/2013 (Feb or March?) would "work" but is hardly nice.  There's a lot more detail in this thread http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/a5e40392-2bf8-432e-89f5-3a948fc3ea7a and the subsequent Connect issue I filed https://connect.microsoft.com/SQLServer/feedback/details/750202/date-picker-in-reporting-services-2008-r2-visual-studio-designer-wrong-formatting#tabs .

    The new SQL 2012 tools that were released a couple of months ago that us VS2012 as the designer still have the same flaw.  I've just tried opening a Microsoft support incident but the issue opening tool seems broken as any selection of a SQL Server product results in 404s :(

    Anyway, the other forum thread may be able to help others or at least help clarify the problem we're experiencing.  I'm apply CU4 for SQL 2012 at the moment plus I saw there's a release of the VS2012 "BIDS" tools to get on the CU4 page - maybe it's a new version?  I'm hoping so :)


    Ian Yates Technical Manager Medical IT Pty Ltd PO Box 501, Carina QLD 4152 Australia Web: www.medicalit.com.au

    Sunday, June 16, 2013 3:16 AM
  • I have found that this problem was fixed for me by SQL Server 2012 SP1 CU5.

    The date pickers in SharePoint Integrated mode now honour the regional settings of the environment.

    This was quite clearly a bug that was fixed - not any problem with the reports/environment settings. (Mike Yin's answer of "This behaviour is by design" is invalid)

    I'm just amazed it took them 1.5 years to fix it.

    Cheers,

       James.


    James Boman - http://www.boman.biz Software Consultant for IPMO - http://www.ipmo.com.au



    • Edited by James Boman Tuesday, August 6, 2013 4:45 AM
    Tuesday, August 6, 2013 4:43 AM
  • Hi James,

    Can you provide some more details on what you've modified?

    I have a server with sharepoint 2013 and sql 2012 sp1 cu6 installed.

    The report's "Localisation" language setting is en-AU.

    My PC and browser are set to en-AU.

    The windows level settings on the sharepoint server are en-AU.

    Sharepoint box was rebooted after I changed the language settings.

    But loading up the report in sharepoint on my machine still shows the date as 11/14/2013 instead of 14/11/2013.


    Jakub @ Adelaide, Australia

    Thursday, November 14, 2013 2:48 AM
  • Jakub,

       Also make sure your Site Collection regional settings are set to en-AU as well. (That's the only one I can think of you may have missed).

    Installing CU5 fixed it for me but I have not tested CU6 - the SQL team have a habit of re-introducing old errors in new patches.

    Cheers,

       James.


    James Boman - http://www.boman.biz Software Consultant for IPMO - http://www.ipmo.com.au

    • Proposed as answer by jakubk Thursday, November 14, 2013 4:02 AM
    Thursday, November 14, 2013 3:24 AM
  • Breathe a sigh of relief! Making that site collection regional change got it working for me. So looks like it's still good in CU6.

    Jakub @ Adelaide, Australia

    Thursday, November 14, 2013 4:03 AM
  • Hi

    The problem is that if you format the date in the query, the data type that SSRS will receive it'll be a string so it complains about the data type, so instead of formatting a date, you want to cast it as date type. Meaning:

      SELECT CAST(ProjectStartDate AS Date) AS ProjectStartDate
          CAST(ProjectFinishDate AS Date) AS ProjectFinishDate
         FROM MSP_Projects
       WHERE ProjectUID = ...

    I also had the exact same problem, and this solved it!

    • Proposed as answer by Pedro Gordo Tuesday, September 16, 2014 1:07 PM
    Tuesday, September 16, 2014 1:07 PM
  • I have a Dev server running SharePoint 2013 SP1 and SQL 2012 SP2.  It has two SSRS instances, one in Native / Report Manager mode and one Integrated with SharePoint. The server, site, report, desktop etc are all set to en-AU.

    Using Internet Explorer, the date parameters work fine on either site.

    Using Chrome and Firefox, the date parameters work fine on the SharePoint site, but show in US MM/DD/YYYY format on the Native / Report Manager site. So this issue seems to only be partially fixed ...

    Thursday, October 23, 2014 4:29 AM
  • HI,

    I try something and have a good result in VisualStudio2010

    First :

    Change a language propertie of 2 zone ( texbox and tablix ) to fr-FR

    Second :

    RightClick in two zone and change a "Number" "properties" to "Date"

    Thursday, November 5, 2015 10:22 AM
  • Appreciate this is an old thread, but for what it's worth I found that the date format shown was dependent on my browser. On the same PC I have IE and Chrome, and IE was using the correct regional settings but Chrome was not. I would have expected Chrome to have picked up the region from the OS settings but seemingly it doesn't.

    In Chrome go to Settings > Show Advanced Settings > Languages > Language and Input Settings.

    Note that there is an 'English' option in addition to 'English (United Kingdom)' and 'English (United States)'. I made sure 'English (United Kingdom)' was the top item.

    • Proposed as answer by kb2704 Monday, January 23, 2017 5:54 AM
    Tuesday, August 30, 2016 11:23 AM
  • This may well solve your issue, which is awesome.  Thanks for sharing the workaround.  

    However, this thread was *originally* about the design time experience, or at least a report viewer control not running in a browser (as evidenced by the many screenshots).  In that case the Windows regional settings, and possibly IE's settings, do play a role.

    The posts about SharePoint are also not design-time nor fat-client report viewer issues but they may well be solved using your method too.  The more info available the better!

    I'd happily forgotten about this issue and now just don't use SSRS anymore as its deployment got more fiddly with time.  We're an ISV so it makes more sense to ship reports embedded in our software than rely on a separate report server.  Other solutions (we're currently using Telerik's) definitely have many annoying shortcomings but things like the US-centric date formatting expressed in this thread are certainly issues I won't miss having :)



    Ian Yates Technical Manager Medical IT Pty Ltd PO Box 501, Carina QLD 4152 Australia Web: www.medicalit.com.au

    Monday, January 23, 2017 6:41 AM
  • Thank Mike, setting Available Values to None was the key for me - then it started showing date only.
    Wednesday, March 15, 2017 4:55 PM