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:

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 and we will update this post.

Related Stories

See all

Stock Adjustments and Email Alerts in ConnectIt

Oct 09, 2014

Further to requests from customers, we will imminently be introducing Sage Stock Adjustments and email alerts into the ConnectIt-Sage50 software. The Stock Adjustment features will provide an additional option off of the QuoteWerks Tools menu called “Create Stock in Sage”. When clicked, this option will open a screen listing the Products that are on the […]


ConnectIt Pro Key Feature #4 – Company Branding

May 29, 2012

This is a feature that perhaps high volume resellers will be interested in, and give you the opportunity to customise the look of the software with your own corporate branding – logos, support and website links, even the images on the buttons(!). By branding the software to your image, you can either match ConnectIt to […]


ConnectIt Pro Key Feature #3 – Multiple Transfer Profiles

May 25, 2012

The traditional ConnectIt products have just one configuration; the configuration is highly flexible, but there is just one. If you need multiple types on transfer – perhaps you are running several disconnected systems or perhaps running more than one company which require information presented differently between systems, then you need to either keep changing the […]