More bugs in SSRS 2k5 ....

Here is another one thats been bothering me for quite some time and I believe for user with regional and language settings to non-US English.

I have my regional settings set to Canadian English. I am using Calender controls to pick up Start, End dates in my report. When I pick a date from calender it automatically converts it behind the scenes to US English I believe. Therefore when I run the report, I get the result set, say from Oct 8 instead of Aug 10. Further, if I select the date as 13 Aug, the following error gets thrown (as a result that the SSRS is not able to identify 13 being any month of course)

"An error occured during local report processing. The value provided for the report parameter 'StartDate' is not valid for its type".

Is there a solution to this problem? The underlying date type is DateTime, so why should it matter which regional setting I use?

Comments anyone?

EDIT

The SSRS controls comes with Calendar property you need to use that, because if you use the NOW SSRS will run your report with current date time in your box, I have used it. But I think you have mistaken TimeZone for DateTime, you can get DateTime in SQL Server while TimeZone is .NET there are work around solutions now and it is implemented in VS2008. Try the thread below for details.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1860968&SiteID=1

|||

I never said I am using TimeZone. Let me try to explain once more. I have my regional settings in Control Panel >> Regional and Language Options>>Regional Options>>Standards and Formats - set to English(Canada) and Location set to Canada, further in the Advanced Tab its also set to English (Canada).

Now back in report, I have defined two report parameters: StartDate and EndDate. Their data type is set to DataTime. Therefore when I preview the report, the parameters display Calenders next to the boxes automatically. I pick a date, say 14 Aug 2007 from this calender. The date is formatted as 14/08/2007 in the text box (Canadian format dd/mm/yyyy). Then I hit the View Report button, and its then that the error gets thrown because for some reason report server/sql server does not accept this date format. Notice that I have not done any thing fancy with the date time parameter, simply out of the box usage.

So, I did this test. I switched the computer settings to English (United States) and wolla...everythings works fine. However thats not desirable as this would require the users to use US-English is all applications such as Word, Excel etc.

The thread you provided the link for does not address my problem unfortunately, it talks about TimeZone in .NET.

I suspect that users with English (UK, AUS, NZ) are also facing same problem. I would highly appreciate if some one can provide a solution to this problem.

|||

Moved to the reporting services forum...

|||? Dint I post in SSRS in first place?|||

SQL Server can do what you want you just need the correct table design, 2005 also comes with GETUTDATE function you can use. Run a search in the BOL for GETUTCDATE and CAST and CONVERT, there is a guideline for SQL Server DateTime design and how to get datetime in the format you want in the links below.

http://www.karaszi.com/SQLServer/info_datetime.asp

http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

|||This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

|||To add to this discussion, I've noticed that Report Manager and Report Server behave differently when viewing the same report. One suffers from the bug and the other doesn't.|||

Well, I do not have SQL Server SP2 installed. Not sure if installing that would make a diff and I can't do that without DBA's consent.

D Wall wrote:

This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

|||

Ok, so may be who ever wrote this Calender control had a grudge against us Canadians. Jus kidding

D Wall wrote:

This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?