Thursday, March 22, 2012

Date parameter format incorrectly interpreted

Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.

My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"

If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.

If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:

" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "

Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?

Thanks and Regards,

RenaHi guys,

I'm having the exact same problem. Any suggestions yet?

My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.

Tks ... Richard|||

I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.

I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.

Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.

Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview

Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.

Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.

I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)

See if it works for you.

Ian

|||

Hi,

Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!

Thanks again

John

|||

Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.

So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?

|||

If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.

Hope that helps,

-Lukasz

|||

Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:

1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).

2. Close Visual Studio 2005 and open it again.

3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)

4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.

Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?

Thanks,

Dan

|||

Hi Lukasz

I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.

In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).

In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.

Many thanks,

Mike.

|||

In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.

One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.

|||

Yes, Browser settings are set to UK English.

But browser settings shouldn't be part of the equation as the problem's in Visual Studio.

Anyway, thanks for your suggestion.

|||

Dear Lukasz,

After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.

I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).

I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.

Best regards and thanks in advance,

Jo?o Costa

No comments:

Post a Comment