Post reply

DateTime Parameter Formats

  • June 12, 2006 at 7:52 am

    #168563

    Hi.

    I need to set up to Reporting Parameters to read the US format for date and time (YYYY/MM/DD).

    The query I am using expects the date to be returned in that format, and the DB (Oracle) is holding tehe data in that format too.

    When setting up the query, I chosse the datetime option so that when the report is compiled, you get the calender picker - only it inputs the date in UK format. If you change it to US format manually and switch to a new field, it simply reverts back to UK format.

    This is failing the query, so I need to know if there's a simple way to get the Report to render the US format for those parameters.

    Any ideas?

    Thanks in advance,

    David

  • June 13, 2006 at 2:51 pm

    #643493

    format the report dt params as string, then in your query use the SQL CONVERT function to change the string to a datetime in the format you require.

    BTW i thought US format was MM/DD/YYYY?

    Kindest Regards,

    Martin

  • June 13, 2006 at 2:54 pm

    #643495

    David
    when in report designer if you go to Report >> report parameters, there select your parameter (under available values label) and define a custom express for the parameter
     
    u have not tried that already,have you ? i never done that before but am guessing it should work.
     
    or can you change ur query so that it converts the parameter in the way it wants?

  • June 14, 2006 at 6:33 am

    #643611

    I am in a similar situation.

    I think in the designer the format of the datepicker depends on the locale of the machine running VS2005.

    When the report is deployed the format depends on the locale of the server running RS2005. If these conflict the behaviour seems to include a kind of toggling of formats!!

    I have a US locale server and an Australia locale development machine and I get chaos if I try to use date parameters.

    The safe option is follow Martin Ns advice and use String type parameters, but then you dont get the date picker (sad).

  • June 14, 2006 at 6:38 am

    #643613

    ex Cevas Data Systems Renato?

  • June 14, 2006 at 6:37 pm

    #643835

    I also have the same problem

     

    even though I have force my date time to UK but when I use date picker always says that the value is not correct.

    so I don't use date picker. I ask user to enter manually

  • June 15, 2006 at 2:42 am

    #643886

    I've followed some guidelines mentioned in these forums and one of them is setting up another dataset that you can use as default settings for the parameters. So I can select to automatically fill in today's, yesterday's, last week's etc etc date range as a string, so no date picker is needed.

    The problem I am seeing is that the format is autmotically filled in as DD/MM/YYYY HH:MM:SS where I only need the DD/MM/YYYY portion.

    My query is failing because of this - but if I then manually take off the HH:MM:SS then it runs fine.

    I'm getting there, and if I find the solution that works 100% I'll post it here.

    Thanks,

    David

  • June 15, 2006 at 3:45 pm

    #644091

    try CONVERT (DATETIME, @Start_Date, 103) where @Start_Date is a varchar (10) in the format DD/MM/YYYY.

    Kindest Regards,

    Martin

  • July 13, 2006 at 12:25 am

    #648871

    I have a problem when I use the date picker to select a date e.g. 02 July 2006 - it converts it to 07/02/2006 which the SQL code reads as 07 Feb 2006 - how do I stop this from happening?

  • July 18, 2006 at 5:46 pm

    #649990

    One way to stop it from happening is to change the parameter data type to String. This means the date picker wont display.

    I suspect this bug has to do with different locale IDs on the report server and client machine.

    I have not had time to look into this but have found an article that may help:

    http://msdn2.microsoft.com/en-us/library/ms155064.aspx

  • September 6, 2006 at 12:59 am

    #658708

    The bug I was seeing was fixed when I applied SP1 to SQL 2005 Reporting Services.

  • July 3, 2007 at 10:03 am

    #716951

    Change Culture for your reportserver page.

    Edit the ReportViewer.aspx webpage. It usually in this directory: "%ProgramFiles%\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\Pages\"

    Add Culture attribute to the @Page declaration like this:

    <%@ Page Language="C#" AutoEventWireup="true" Inherits="Microsoft.ReportingServices.WebServer.ReportViewerPage" Culture="cs-CZ"%>

    Now the date from the datepicker is presented in the culture specific format (for czech it is dd.mm.yyyy).

    You can use Culture="en-GB" for the United Kingdom format of DD/MM/YYYY

  • July 4, 2007 at 2:44 am

    #717086

    I'm with Terry on this one, just change the language settings manually in the rdl, although it's easier to find if you just "view code" from Visual Studio, then do a CTRL F and look for "en-".

     

    I've found the default behaviour to return a US date if you use a string, but it seems to change it dynamically if you use a date time depending on your SQL Server locale, I don't know if that's because I've service packed it tho....

  • June 10, 2008 at 6:48 am

    #826592

    Has anybody since the last post came with a possible suggestion?

    Is it now based on the server locale

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply