none
UK Datetime parameter problem SQL 2005 RS RRS feed

  • Question

  • Hi,

    I am having a problem with the DateTime parameter option in SQL 2005 reporting services.

    Note that Server, workstation and database are set to British/English (UK) date/time localisation (dd/MM/yyyy) for example 25th December 2005=25/12/2005.

     

    I create a simple query with a parameter on a datetime column and set the report parameter to Data type: DateTime in the report parameters dialog.

     

    On preview or after deployment the following behaviour is observed.

     

    Selecting 1st February 2006 in the datetime picker control populates the date parameter entry field with

     

    01/02/2006 = 1st February 2006  (the correct UK format as expected)

     

    When View Report is clicked the datetime parameter field magically changes to

     

    02/01/2006

     

    So forcing the datetime values to US format MM/dd/yyyy, the report runs OK but does not return data restricted to the required date.

     

    If we select 25th February 2006 the datetime parameter field populates with

     

    25/02/2006 = 25th February 2006 (the correct UK format as expected)

     

    When View Report is clicked the datetime parameter changes to 02/01/2006 and an error is displayed

     

    An error occurred during local report processing.

    The value provided for the report parameter X is not valid for its type.

     

    Is there a solution to this?

     

    Thanks,

     

    Neil

     

    Friday, February 17, 2006 3:20 PM

Answers

  • On the server side it is possible that your Browser cached the output of the report.  In IE you can refresh using Ctrl+F5, which by passes the cache.

    In RD - you need to set your VS to 'Same as Windows' under Tools --> Options --> International Settings.  Then in the Regional And Language Settings control panel, change you locale to your desired one.  Now when you preview the report, it should just work for you.

    -Lukasz

    Wednesday, May 24, 2006 10:58 PM
    Moderator
  • JonS1

    Try installing SQL Server 2005 SP1 to the Visual Studio 2005 development environment. This seemed to correct the issue where non US dates swap between US and non US formats each time the View Reports button is clicked in the Preview tab.

    Tuesday, June 13, 2006 11:47 PM

All replies

  • Hello Neil,

    I'm trying to repro this issue, but am not seeing it. For me, the date picker always behaves according to the browser localization, and the date format never "magically changes".

    Do you have any Language tags in the RDL for which you see the problem?

    What is the most favored browser localization (Tools >> Internet Options >> Languages)?

    Friday, February 17, 2006 4:30 PM
    Moderator
  • Hi Mike,

    Language setting is en-GB in the rdl so looks OK.

    I'm using IE 6 and the language setting is English(United Kingdom) [en-gb]

    When you are trying to replicate make sure you got you're whole environment - server, worksation and database with English GB settings.

    I've been googling and I've found someone with the same problem on another forum

    http://forums.asp.net/1190731/ShowPost.aspx

    Thanks for your help,

    Neil

    Friday, February 17, 2006 5:09 PM
  • Neil,

    Could you kindly post a sample RDL for which you see the problem?

    Friday, February 17, 2006 5:16 PM
    Moderator
  • Here's the code:

    <?xml version="1.0" encoding="utf-8"?>
    <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
      <DataSources>
        <DataSource Name="EDI11">
          <DataSourceReference>EDI1</DataSourceReference>
          <rd:DataSourceID>61785b91-6af2-4535-aa33-e08e947808d8</rd:DataSourceID>
        </DataSource>
      </DataSources>
      <BottomMargin>2.5cm</BottomMargin>
      <RightMargin>2.5cm</RightMargin>
      <PageWidth>21cm</PageWidth>
      <ReportParameters>
        <ReportParameter Name="LAST_DATE">
          <DataType>DateTime</DataType>
          <Prompt>LAST_DATE</Prompt>
        </ReportParameter>
      </ReportParameters>
      <rd:DrawGrid>true</rd:DrawGrid>
      <InteractiveWidth>8.5in</InteractiveWidth>
      <rd:GridSpacing>0.25cm</rd:GridSpacing>
      <rd:SnapToGrid>true</rd:SnapToGrid>
      <Body>
        <ColumnSpacing>1cm</ColumnSpacing>
        <ReportItems>
          <Table Name="table1">
            <Footer>
              <TableRows>
                <TableRow>
                  <TableCells>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox8">
                          <rd:DefaultName>textbox8</rd:DefaultName>
                          <ZIndex>9</ZIndex>
                          <Style>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingBottom>2pt</PaddingBottom>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                          </Style>
                          <CanGrow>true</CanGrow>
                          <Value />
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox9">
                          <rd:DefaultName>textbox9</rd:DefaultName>
                          <ZIndex>8</ZIndex>
                          <Style>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingBottom>2pt</PaddingBottom>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                          </Style>
                          <CanGrow>true</CanGrow>
                          <Value />
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox10">
                          <rd:DefaultName>textbox10</rd:DefaultName>
                          <ZIndex>7</ZIndex>
                          <Style>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingBottom>2pt</PaddingBottom>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                          </Style>
                          <CanGrow>true</CanGrow>
                          <Value />
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox7">
                          <rd:DefaultName>textbox7</rd:DefaultName>
                          <ZIndex>6</ZIndex>
                          <Style>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingBottom>2pt</PaddingBottom>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                          </Style>
                          <CanGrow>true</CanGrow>
                          <Value />
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox13">
                          <rd:DefaultName>textbox13</rd:DefaultName>
                          <ZIndex>5</ZIndex>
                          <Style>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingBottom>2pt</PaddingBottom>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                          </Style>
                          <CanGrow>true</CanGrow>
                          <Value />
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                  </TableCells>
                  <Height>0.58201cm</Height>
                </TableRow>
              </TableRows>
            </Footer>
            <Left>0.25cm</Left>
            <DataSetName>ftpstatus_ds</DataSetName>
            <Top>1.25cm</Top>
            <ZIndex>1</ZIndex>
            <Width>26.5cm</Width>
            <Details>
              <TableRows>
                <TableRow>
                  <TableCells>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="EDI_AC_CODE">
                          <rd:DefaultName>EDI_AC_CODE</rd:DefaultName>
                          <ZIndex>4</ZIndex>
                          <Style>
                            <BorderStyle>
                              <Default>Solid</Default>
                            </BorderStyle>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingBottom>2pt</PaddingBottom>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                          </Style>
                          <CanGrow>true</CanGrow>
                          <Value>=Fields!EDI_AC_CODE.Value</Value>
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="FTP_HOST">
                          <rd:DefaultName>FTP_HOST</rd:DefaultName>
                          <ZIndex>3</ZIndex>
                          <Style>
                            <BorderStyle>
                              <Default>Solid</Default>
                            </BorderStyle>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingBottom>2pt</PaddingBottom>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                          </Style>
                          <CanGrow>true</CanGrow>
                          <Value>=Fields!FTP_HOST.Value</Value>
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="LAST_ERR_MSG">
                          <rd:DefaultName>LAST_ERR_MSG</rd:DefaultName>
                          <ZIndex>2</ZIndex>
                          <Style>
                            <BorderStyle>
                              <Default>Solid</Default>
                            </BorderStyle>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingBottom>2pt</PaddingBottom>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                          </Style>
                          <CanGrow>true</CanGrow>
                          <Value>=Fields!LAST_ERR_MSG.Value</Value>
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="LAST_ERR_DATE">
                          <rd:DefaultName>LAST_ERR_DATE</rd:DefaultName>
                          <ZIndex>1</ZIndex>
                          <Style>
                            <BorderStyle>
                              <Default>Solid</Default>
                            </BorderStyle>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingBottom>2pt</PaddingBottom>
                            <Format>dd/MM/yyyy HH:mm</Format>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                          </Style>
                          <CanGrow>true</CanGrow>
                          <Value>=Fields!LAST_ERR_DATE.Value</Value>
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="ERR_LOCKED">
                          <rd:DefaultName>ERR_LOCKED</rd:DefaultName>
                          <Style>
                            <BorderStyle>
                              <Default>Solid</Default>
                            </BorderStyle>
                            <TextAlign>Right</TextAlign>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingBottom>2pt</PaddingBottom>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                          </Style>
                          <CanGrow>true</CanGrow>
                          <Value>=Fields!ERR_LOCKED.Value</Value>
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                  </TableCells>
                  <Height>0.58201cm</Height>
                </TableRow>
              </TableRows>
            </Details>
            <Header>
              <TableRows>
                <TableRow>
                  <TableCells>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox2">
                          <rd:DefaultName>textbox2</rd:DefaultName>
                          <ZIndex>14</ZIndex>
                          <Style>
                            <BorderStyle>
                              <Default>Solid</Default>
                            </BorderStyle>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingBottom>2pt</PaddingBottom>
                            <BackgroundColor>LightGreen</BackgroundColor>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                          </Style>
                          <CanGrow>true</CanGrow>
                          <Value>Code</Value>
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox3">
                          <rd:DefaultName>textbox3</rd:DefaultName>
                          <ZIndex>13</ZIndex>
                          <Style>
                            <BorderStyle>
                              <Default>Solid</Default>
                            </BorderStyle>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingBottom>2pt</PaddingBottom>
                            <BackgroundColor>LightGreen</BackgroundColor>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                          </Style>
                          <CanGrow>true</CanGrow>
                          <Value>Host</Value>
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox4">
                          <rd:DefaultName>textbox4</rd:DefaultName>
                          <ZIndex>12</ZIndex>
                          <Style>
                            <BorderStyle>
                              <Default>Solid</Default>
                            </BorderStyle>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingBottom>2pt</PaddingBottom>
                            <BackgroundColor>LightGreen</BackgroundColor>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                          </Style>
                          <CanGrow>true</CanGrow>
                          <Value>Last Error</Value>
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox5">
                          <rd:DefaultName>textbox5</rd:DefaultName>
                          <ZIndex>11</ZIndex>
                          <Style>
                            <BorderStyle>
                              <Default>Solid</Default>
                            </BorderStyle>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingBottom>2pt</PaddingBottom>
                            <BackgroundColor>LightGreen</BackgroundColor>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                          </Style>
                          <CanGrow>true</CanGrow>
                          <Value>Occured</Value>
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox11">
                          <rd:DefaultName>textbox11</rd:DefaultName>
                          <ZIndex>10</ZIndex>
                          <Style>
                            <BorderStyle>
                              <Default>Solid</Default>
                            </BorderStyle>
                            <TextAlign>Right</TextAlign>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingBottom>2pt</PaddingBottom>
                            <BackgroundColor>LightGreen</BackgroundColor>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                          </Style>
                          <CanGrow>true</CanGrow>
                          <Value>Lock Flag</Value>
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                  </TableCells>
                  <Height>0.58201cm</Height>
                </TableRow>
              </TableRows>
            </Header>
            <TableColumns>
              <TableColumn>
                <Width>3.25cm</Width>
              </TableColumn>
              <TableColumn>
                <Width>6.25cm</Width>
              </TableColumn>
              <TableColumn>
                <Width>11.25cm</Width>
              </TableColumn>
              <TableColumn>
                <Width>3.25cm</Width>
              </TableColumn>
              <TableColumn>
                <Width>2.5cm</Width>
              </TableColumn>
            </TableColumns>
            <Height>1.74603cm</Height>
          </Table>
          <Textbox Name="textbox1">
            <rd:DefaultName>textbox1</rd:DefaultName>
            <Width>12.69841cm</Width>
            <Style>
              <PaddingLeft>2pt</PaddingLeft>
              <PaddingBottom>2pt</PaddingBottom>
              <FontFamily>Times New Roman</FontFamily>
              <FontSize>18pt</FontSize>
              <Color>#365838</Color>
              <PaddingRight>2pt</PaddingRight>
              <PaddingTop>2pt</PaddingTop>
            </Style>
            <CanGrow>true</CanGrow>
            <Height>0.8381cm</Height>
            <Value>FTP Status</Value>
          </Textbox>
        </ReportItems>
        <Height>3.75cm</Height>
      </Body>
      <rd:ReportID>cb3e8c2e-f280-4506-a458-0e85cd1cd107</rd:ReportID>
      <LeftMargin>2.5cm</LeftMargin>
      <DataSets>
        <DataSet Name="ftpstatus_ds">
          <Query>
            <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
            <CommandText>SELECT EDI_AC_CODE, FTP_HOST, LAST_ERR_MSG, LAST_ERR_DATE, ERR_LOCKED
     from tblEDI_TPCFG_IO
    WHERE EDI_STATUS_FLAG='Y' AND FTP_ACTIVE_FLAG='Y'
    AND LAST_ERR_DATE>=(@LAST_DATE)</CommandText>
            <QueryParameters>
              <QueryParameter Name="@LAST_DATE">
                <Value>=Parameters!LAST_DATE.Value</Value>
              </QueryParameter>
            </QueryParameters>
            <DataSourceName>EDI11</DataSourceName>
          </Query>
          <Fields>
            <Field Name="EDI_AC_CODE">
              <rd:TypeName>System.String</rd:TypeName>
              <DataField>EDI_AC_CODE</DataField>
            </Field>
            <Field Name="FTP_HOST">
              <rd:TypeName>System.String</rd:TypeName>
              <DataField>FTP_HOST</DataField>
            </Field>
            <Field Name="LAST_ERR_MSG">
              <rd:TypeName>System.String</rd:TypeName>
              <DataField>LAST_ERR_MSG</DataField>
            </Field>
            <Field Name="LAST_ERR_DATE">
              <rd:TypeName>System.String</rd:TypeName>
              <DataField>LAST_ERR_DATE</DataField>
            </Field>
            <Field Name="ERR_LOCKED">
              <rd:TypeName>System.Int32</rd:TypeName>
              <DataField>ERR_LOCKED</DataField>
            </Field>
          </Fields>
        </DataSet>
      </DataSets>
      <Width>27.17019cm</Width>
      <InteractiveHeight>11in</InteractiveHeight>
      <Language>en-GB</Language>
      <TopMargin>2.5cm</TopMargin>
      <PageHeight>29.7cm</PageHeight>
    </Report>

    Thanks

    Friday, February 17, 2006 5:25 PM
  • Hmmm ...

    I can't get the parameter value to flip to the wrong format. I'll keep working on this, and will post if I find anything.

    Friday, February 17, 2006 5:41 PM
    Moderator
  • Just in case it may be relevant the platform is Server 2003 Standard x64 Edition and database is also Standard Edition (x64).

     

    Monday, February 20, 2006 1:55 PM
  • Further testing today shows the problem now only manifests itself in the preview tab of the development environment as the freshly deployed report is now behaving as expected. Strange- maybe there was some sort of caching going on with the deployed report showing us an older version?

    Regarding the problem continuing within the IDE, perhaps the international settings within the development environment aren't taking effect in the preview tab?

    Note that Tools>Options>International Settings are set to English

    Thanks,

     

     

    Wednesday, February 22, 2006 3:01 PM
  • Hi

    we are having the same problem with the date picker  when used to select for parameters for report in SQL Server Reporting Services using Visual Studio 2005.

    The date picker shows the current date in UK format but when you select a date with the day greater than 12 the same error occurs as it has been converted into a US date.

    We have tried changing the Globalization settings for culturein the web.config file to en-GB ie culture="en-GB".

    However this didn't seem to work.

    Regards

      Arnold

     

     

    Thursday, May 18, 2006 10:29 AM
  • On the server side it is possible that your Browser cached the output of the report.  In IE you can refresh using Ctrl+F5, which by passes the cache.

    In RD - you need to set your VS to 'Same as Windows' under Tools --> Options --> International Settings.  Then in the Regional And Language Settings control panel, change you locale to your desired one.  Now when you preview the report, it should just work for you.

    -Lukasz

    Wednesday, May 24, 2006 10:58 PM
    Moderator
  • Hi,

    The problem persists for us in the Visual Studio IDE preview tab but as the deployed reports are OK it's not a major problem for us.

    As I said in an earlier post I do not think the international settings within the development environment are taking effect in the preview tab- hence the problem when previewing reports in the IDE.

     

    Thursday, May 25, 2006 7:52 AM
  • I'm having this problem too. Yesterday everything ran fine but then the month and day were the same digit, 6! So now I have to run reports for 6th June to make sure they run properly.

    Couldn't see this bug mentioned in the SS2005 SP1 release notes.

    Wednesday, June 7, 2006 11:17 AM
  • Similar here.  Except with an extra twist! 

    Two date parameters, start & end (datetime), reports work fine in both VS2003 and deployed to the web server.  However, I have a hyperlink from report A to run report B, passing date parameters to report B.  The date format is switched in the process from UK to US and I either get the error above (type mismatch) or the selection period is wrong (day/month transposed).

    It's not a native SQL thing, the raw sql is fine in Query Analyzer.

    It's not the individual reports, they both work independantly in both the IDE and deployed.

    I've checked all regional settings and they are set to English-UK, except VS2003 which is English-US (I can't change it to same as Windows, there is no option to do so).  However, each individual report IS set to English-UK.  Don't really want to re-install VS2003 but if it's the only way... Does anyone know for sure?  I would have thought that changing the report to English-UK is enough because, as I say, each report runs fine when run directly in the browser or in VS2003.

    I've even tried forcing the order of the components of the date to the order in a UK date using DAY(Parameters!Start_date.Value), MONTH, YEAR functions but still no joy.  Combining with Dateserial also makes no difference.

    Seems that someone has left English-US hardcoded somewhere in RS!!!  Or is there a true reason?  Answers anyone please?  Thanx. 

    Wednesday, June 7, 2006 1:56 PM
  • PS I could try passing the date as a string but then of course when I run the receiving report standalone I won't have the date picker... and then there's all the validation of what the user types etc etc  But why should I have to do this anyway?  C'mon, let's have a fix for this obvious BUG please!

    Thursday, June 8, 2006 9:46 AM
  • When report server interprets the date parameter, it is interpreted using the report language.  The default report language (added by report designer) is based on the language of VS you installed.  Since the English version of VS is en-us when using an English report designer.  In the RDL for the report you'll see a property called <Language>en-US</Language>.  It is available at the following path Report\Language.

    If you don't provide a value, the value assumed is the same as the report server.

    If you want your reports to operate on UK dates, always, then you should set the value of the Language element to en-UK.  If you want your reports to always be determined by the end-user context, then set it to User!Language.  However this can cause problems when resolving currency symbols (same value but pounds vs dollars makes a big difference in interpretation ;-)).

    If when you use a particular date, you want to use a particular culture (say the user's input language) you can use the .Net framework functions for handling dates.  See the System.Globalization namespace.  You can use these in report expressions.

    http://msdn2.microsoft.com/en-us/library/system.globalization.datetimeformatinfo(VS.80).aspx

    Hope that helps,

    -Lukasz

     

     

    Thursday, June 8, 2006 10:43 PM
    Moderator
  • Thanks Lukasz, this is very helpful. Although I hadn't yet spotted the problem in my own reports (haven't built many yet), the code for the ones I have done does indeed have <Language>en-US</Language>.

    Isn't it a bit presumptious of Report Designer to assume you want to use American culture just because you've got an English Visual Studio installed? :-)

    Thanks again,

    Rich

    Friday, June 9, 2006 8:04 AM
  • This value is exposed as the language property of the report which can be changed to English (United Kingdom); this appears as 'en-GB' in the code. But it still makes no difference.
    Friday, June 9, 2006 9:41 AM
  • JonS1,

    Can you an RDL snippet that desmonstrates the problem.  I tried this the other day and it worked OK for me. 

    Is your client specifying the accept language HTTP header? IE does this based on the language you set in the Tools--> Options--> Language button.

    -Lukasz

     

    Friday, June 9, 2006 10:01 PM
    Moderator
  • Point taken :-).  I'll see if there is a way to control this behavior today.

    -Lukasz

    Friday, June 9, 2006 10:02 PM
    Moderator
  • Ok, here's your solution for the default value issue.  Albert, another member of the RS team, sent these comments to me:

    You can set the default by editing the report template %ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject\Report.rdl. 

    Remove the line

    <rd:InitialLanguage>true</rd:InitialLanguage>

    Replace it with

    <Language>en-GB</Language>

     

    Also, we have a bug logged to change the default to be the value in the regional setting of the OS.

    -Lukasz

    Friday, June 9, 2006 10:44 PM
    Moderator
  • Am I right in stating that this solution only changes the default language of the report to en-GB or whatever you may specifiy. The initial issue described in the post where the datetime parameter changes from en-GB to en-US format each time you click the View Report button in the Preview of Report Designer still persists. Is there a solution to this or is it a bug?
    Monday, June 12, 2006 11:21 PM
  • The language for UK is en-GB; en-UK is not recognised by my VS2005. The problem lies within VS2005 itself. As a further example, if I run a report for 12th June 2006, the date changes to 6th December 2006 then the report runs. If I run the report again the date changes back to 12th June 2006 (which is what was originally wanted) and runs OK. Running it again and I get 6th December ... all very entertaining.

    I have had a number of email alerts about this but they don;t all appear on this bulletin board.

     

    Tuesday, June 13, 2006 8:08 AM
  • Can you file an issue throught the http://connect.microsoft.com/ site.  I didn't reproduce this behavior when I tried various combinations of 1) browser language, 2) report language, 3) report parameter language.

    -Lukasz

    Tuesday, June 13, 2006 5:58 PM
    Moderator
  • You need to find the very much hidden 'next' button at the bottom right of the page. You'll find we've started a second page with our conversation.

    Can you tell me

    1) does this happen only in VS or does it also happen when the report is deployed to the report server?

    2) what is your input language set to in the regional and language settings control panel?

    3) what is the language you have set in your browser?

    4) can you provide your RDL(s)

    Thanks,

    -Lukasz

     

    Tuesday, June 13, 2006 6:04 PM
    Moderator
  • JonS1

    Try installing SQL Server 2005 SP1 to the Visual Studio 2005 development environment. This seemed to correct the issue where non US dates swap between US and non US formats each time the View Reports button is clicked in the Preview tab.

    Tuesday, June 13, 2006 11:47 PM
  • Lukasz

    Replacing the <rd:InitialLanguage> tag with the <Language> tag in the template report is a workaround if you use the Add New Item option to add an empty report to the project. However if you use the Add New Report option to add a new report via the Wizard, the resulting RDL XML file is erroneous. The cause appears to be that the Wizard inserts a <Language>en-US</Language> tag regardless of the content in the report template thus causing a duplication of <Language> tags. The resulting RDL XML fails validation.

    Wednesday, June 14, 2006 1:36 AM
  •  

    I have exactly the same problem with reporting services 2005.

    In the datetime report parameter, the month and day values switch places on every click on the "View Report" button. I have taken Microsoft's advise and changed the report's language property to "en-GB". Afterwards, I deployed the report to the report server. Now the problem is fixed only on my own computer (the computer where I developed the reports using Visual Studio).

    The problem still remains on every other computer....

    I'm trying to reproduce the problem on my computer so I can fix it...

    I checked the following settings:

     Internet Explorer: Tools->Internet Options-> Languages...

    Control Panel: Regional and language options->Standards and formats, Location.

    They are all the same on all computers. So, if two computers with the same regional options viewing the same report on the same report server, where the "language" property of the report is "en-GB". Why is the behavior different for the two computers?

    Does anyone have some idea how to fix this problem?

    It is a serious bug when the month is over 12 ....

    I appreciate any help on the matter.

    Best regards,

    Eyal

    Wednesday, June 14, 2006 7:45 AM
  • Install SQL Server 2005 SP1 on the development machine.
    Wednesday, June 14, 2006 7:49 AM
  • If you're using report designer on the other machines, then try installing SQL 2005 SP1 on that machine.  If you're accessing using a browser, check the language setting in your browser.

    -Lukasz

    Wednesday, June 14, 2006 6:23 PM
    Moderator
  • Chamitha is correct - we infact did fix a bug on related to this in SP1. It fixes the issue in Report Designer Preview.  No wonder I couldn't reproduce it as I have SP1 on my computers :-).

    Thanks Chamitha!

    -Lukasz

    Wednesday, June 14, 2006 11:04 PM
    Moderator
  • It seems I'm not the only one ripping my hair out over this.

    I have a subtly different issue.  And is probably about as frustrating as it's likely to get.

    Everything works fine in the report-preview in BI dev-studio.  I try to run the same report, on the same machine on on my asp.net app.  I pass a start-date and an end-date.

    To test that I was getting the right dates coming through I placed the start date in a text-box.  The start and end-dates are used to filter appointments.  Now the date in the text-box displays correctly.  But the dates that are obviously passed through the SQL command are obviously going into en-US format (I'm en-GB).

    This just seems ludicrous.  My machine settings are all en-GB, I have set the locale to en-GB on the report.  Why can't it be consistent?  Is this another bug, or am I missing something?

     

    Monday, July 3, 2006 4:45 PM
  • Any ideas?
    Tuesday, July 4, 2006 10:42 PM
  • I had the same problem and this solved it - thanks. However SSRS still seems to expect the default date/time to be in US format. Is this a known bug or is there a way round this too?
    Wednesday, July 26, 2006 11:11 PM
  • Hi

    We have installed SQL 2005 SP1 and Visual Studio 2005 SP1 (beta).

    We get this formatting date problem when running reports in Visual Studio 2005 and once published in Reporting Services.

    When running the reports and specifying the date format with in a parameter as dd/mm/yy, the report fails as it trys to read the date as mm/dd/yy.

    Phil

     

     

     

     

    Tuesday, October 3, 2006 3:11 PM
  • I had this problem also - did everything mentioned & still got the problem - then remembered to put SQL SP1 for SQL client on the client machine with VS2005 !!!!

     

    regards

    Wednesday, October 4, 2006 1:02 PM
  • That now works great. Thanks for your help.

     

    Thursday, October 5, 2006 2:42 PM
  • Following Lukasz suggestion I updated and RDL file to <Language>en-UK</Language>. 

    When opening the RDL in VS2005 I now get the following value.  Deserialization failed: en-UK is not a valid value. Line 1196, position 20.

    Can anyone assist ?

    Tuesday, October 17, 2006 8:53 PM
  • Hi there,

     

    I am located in the Netherlands, developing reports on a US visual studio which is set to English. Regional settings on our dev servers are set to Dutch. Production server has regional settings set to US English. I am developing reports for people who have local clients in Russia and Greece. Maybe you can imagine I replaced my datetime picker with an ugly dropdown list with dates a long time ago.

     

    My suggestion would be:

    - display the date picker using regional settings / report language / whatever

    - when a date is picked, lets say 12/4/2007 (12th of april) then put the regional date string into the .LABEL field of the parameter

    - fill the .VALUE field with a string which can not be misinterpreted by an SQL query, like '12 APR 2007'

     

    Currently, the datetime picker leaves the LABEL unused. Am I missing something or would make this life a much easier?

     

    Regards,

    Jeroen

    Thursday, April 26, 2007 1:30 PM
  •  

    Thanks to everyone in this thread - I now have all my issues with this sorted.

     

    The changing the RDL and its initial language doesn't seam to make any difference for me.  Either of a template or an active file.  The two fixes I've needed to use are:

     

    Manually edit the ReportViewer.aspx on the server to add Culture="en-GB" to the @page directiveso that the date picker puts the date into the date box the UK way round.

    Use the Internet Explorer lanugage settings to put en-GB at the top of the language list on my client machines (I've never neaded to press that button before today).

     

    My scenario is Reporting Services 2005 SP2, Visual Studio 2005 SP1

     

    Hope this helps someone

    Friday, February 15, 2008 4:53 PM
  • Lukasz Thread is good for the new Report

     

    But What about Old one

     

    Here is the Solution:--

     

    View code for the report

     

    Just near bottom of the report Change Language en-US to en-GB

     

    Compile and sorted

    Thursday, July 24, 2008 9:55 AM