DEVELOPMENT: issue with DateTime datatype in SQL Server Reporting Services

We recently upgraded a client’s SQL Server 2000 business intelligence reports to SQL Server 2005, and from a Visual Studio .net 1.1 software development environment to Visual Studio 2005.

It should be a simple transition we thought, which it was… apart from some anomalies around the datetime parameters in SQL Server 2005 Reporting Services.

There were a couple of things we noticed going wrong. Firstly, defaulting a value to 2/11/2008 (dd/MM/yyyy) for example would come through on the report as 11/2/2008. Secondly, when changing the date from 11/2/2008 using the datepicker to 12/2/2008 would display 2/12/2008 in the textbox. Basically, the dd/MM/yyyy was getting transposed to MM/dd/yyyy and back again.

Also there were “the value provided for the report parameter [parameter name] is not valid for its type” selecting the dates and running the report when dates like 13/2/2008 or 2/13/2008 were selected.

Searching the internet we found a number of reports of similar issues with several different fixes being required. Unfortunately no blog or knowledgebase article seemed to have all the information in one place, so we have centralized the details of fixes here.

We hope that you find it useful.

Report Server changes

1) First, ensure that all up to date SQL Server Service Packs are installed – there are reports that the problem for server-side issues were fixed in Services Pack 1 but the reintroduced in Service Pack 2. At time of writing the issue is understood to be fixed with patch releases to date.

SQL Server Service Packs and Hot Fixes can be found by using Windows Update or the Microsoft Download Centre: http://download.microsoft.com/

2) Next, we need to change (or add) the Culture value in Report.aspx to:
<%@ Page language=”c#” Codebehind=”Report.aspx.cs” AutoEventWireup=”false” Inherits=”Microsoft.ReportingServices.UI.ReportWrapperPage” EnableEventValidation=”false” Culture=”en-GB”%>
<%@ Page language=”c#” Codebehind=”Report.aspx.cs” AutoEventWireup=”false” Inherits=”Microsoft.ReportingServices.UI.ReportWrapperPage” EnableEventValidation=”false” Culture=”en-GB” %>%@ Page language=”c#” Codebehind=”Report.aspx.cs” AutoEventWireup=”false” Inherits=”Microsoft.ReportingServices.UI.ReportWrapperPage” EnableEventValidation=”false” Culture=”en-GB” %

Report.aspx can be found in C:Program FilesMicrosoft SQL ServerMSSQL.?Reporting ServicesReportManagerPages

3) Finally, change the culture in the IIS to “en-GB”: Control Panel Administrative Tools Internet Information Services (IIS) Manager Properties ASP.NET tab Edit Configuration button Application tab Globalisation Settings section Culture

Report Definition changes
The blog entry we found on this said to ensure that the language for the report itself is set to your country (Layout view, Report Properties). We couldn’t find this place in the UI to change this, so instead did a Find and Replace on “en-US” for “en-GB”.

Changing the Language and redeploying the reports should now fix the problem for the user. If however the users still get problems, then ask them to check the Language Settings of their browser are “English (United Kingdom) [en-gb]”

Development machine changes

Ok – so you have fixed the problem for your users, but Visual Studio still doesn’t behave quite right. We need to change the Regional Settings slightly.

First close visual studio, then change the Regional Settings on your development machine. Customize the short date format to “yyyy-MM-dd” (Control Panel Regional and Language Options Regional Options Customize button Date) This fixes the report Preview from Visual Studio.

Note that this may cause problems with other applications. If anyone has a better fix for this particular issue, please email development@hilltopsit.co.uk and we will update this post.