locked
Different behaviour between preview and ReportServer for Datetime conversion RRS feed

  • Question

  • I have created a report that includes the following dataset:

    SELECT SUM(LoadedPremium)/1000000 AS [Gross Annual New Business Premium (millions)]
    FROM vwNewBusiness
    WHERE Product <> 'LPI' AND IssueDate BETWEEN CAST('2008/07/01' AS datetime) AND CAST('2009/06/30' AS datetime)

    Initially I didn't bother with the CAST statements and the report worked fine in Preview mode. I Deployed it to the ReportServer and when I run it, I get this error message:

    An error has occurred during report processing. Cannot read the next data row for the data set NewBusinessCount. The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

    I don't have a PC and use a Winterm to connect to a Domain where I run IE and link to the ReportServer web page. I use RDP to connect to a VM that has the SQL Server Client tools installed where I edit and preview my reports. I get the error if I connect to the Report Server from the domain but I don't get the error if I connect to the report server from the VM where the client tools are installed. As far as I can see, the settings in the Domain for date style are identical to the settings on the VM.

    Anybody got any ideas?
    Wednesday, September 9, 2009 11:04 PM

Answers

  • Hi Nick,

    By default, while previewing the report in Visual Studio, the date format will be selected based on the currect machine(means development box)'s culture.

    While viewing the report in the Report Server, if we have set a UICulture for the Report Server, the data format will be selected based on the Internet Explorer's culture on the client.
    We can follow these steps to check this topic:
    1.In Internet Explorer, clikc the menu "Tools" -- > "Internet Options"
    2.Click "Language"
    3.Add a language to the language list, and then move the used language to top.

    Additional, we can add the following configuration in the web.config of the report server to force the report server using a specified UICulture:
    <globalization requestEncoding="utf-8" responseEncoding="utf-8"  uiCulture="en-us" />

    If you have any more questions, please feel free to ask.

    Thanks,
    Jin Chen
    Jin Chen - MSFT
    • Marked as answer by Jerry Nee Friday, September 18, 2009 8:50 AM
    Friday, September 11, 2009 10:18 AM

All replies

  • Hi Nick,

    By default, while previewing the report in Visual Studio, the date format will be selected based on the currect machine(means development box)'s culture.

    While viewing the report in the Report Server, if we have set a UICulture for the Report Server, the data format will be selected based on the Internet Explorer's culture on the client.
    We can follow these steps to check this topic:
    1.In Internet Explorer, clikc the menu "Tools" -- > "Internet Options"
    2.Click "Language"
    3.Add a language to the language list, and then move the used language to top.

    Additional, we can add the following configuration in the web.config of the report server to force the report server using a specified UICulture:
    <globalization requestEncoding="utf-8" responseEncoding="utf-8"  uiCulture="en-us" />

    If you have any more questions, please feel free to ask.

    Thanks,
    Jin Chen
    Jin Chen - MSFT
    • Marked as answer by Jerry Nee Friday, September 18, 2009 8:50 AM
    Friday, September 11, 2009 10:18 AM
  • Hi Jin

    Thanks but I found that the language setting on the domain IE was the same as the language on the VM which was English (New Zealand). One other difference is that the VM is Windows Server 2008 and the browser is IE8 whereas in the domain, the browser is IE6. This may account for the different behaviour depending on whether I connnect to the Report Server from the VM or the domain.

    So I haven't solved my date problem but have found a workaround involving using hidden parameters of type date to allow me to refer to a fixed value for a date without using a string constant.

    Regards,
    Nick
    Friday, September 18, 2009 11:50 PM