Problem with date format with date parameter in SSRS 2005 report

louise001
louise001 used Ask the Experts™
on
Hi,
In SSRS 2005 I have a report with two date parameters and in the BIDS environment when I run the report if I've selected a date which can't be read into mm/dd/yyyy such as 30/06/2009, I get this error: "An error occurred during local report processing. The value provided for the report parameter 'End' is not valid for its type." If I select two dates which can be read into mm/dd/yyyy the report runs fine and dates are displayed in the UK date format so 06/07/2009 is 6 July not 7 June.

Some more information:
1. In the code for the .rdl file I've changed en-US to en-GB which is my usual practice
2. In the report designer, the parameters for the start and end date are non-queried, default values entered in text box as 01 Jan 1980 for start date and 31 Dec 2049 for end date
3. In the report designer, the t-SQL executed by the report is EXEC MyStoredProc @Start, @End and in the stored procedure I've set the date format to dd/mm/yy (btw the stored procedure runs fine in SQL Server).
4. The server OS for the installation of SSRS2005 has UK language and regional settings

Thanks in advance for any help.

Louise
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ValentinoVBI Consultant
Most Valuable Expert 2011

Commented:
Being from Belgium myself I believe we share the same date format.  As for SSRS, I always use the following format: yyyy/mm/dd, at least, internally.  This is one of the formats that SQL Server understands by default.
My Report parameters are set up as DateTime.  These can be passed to a stored procedure's parameter of type datetime in the regular way ("=Parameters!YourDateParam.Value"), without any reformatting.

The report's Language parameter does not affect this, it only affects the way dates, numbers and such are displayed.
ValentinoVBI Consultant
Most Valuable Expert 2011

Commented:
Forgot to mention: instead of using "EXEC your_sproc ....", why don't you try the following.

In your dataset, instead of using Text as Command Type, try StoredProcedure.  As query string just put the name of your stored procedure.  Then use the Parameters tab to give pass values to your SPs parameters.

That's the regular way to call a stored procedure on SQL Server.

Hope this helps you out.

Author

Commented:
Hi Valentino,

Thanks for responding. I've tried entering 1980/01/01 and 2049/12/31 as the non-queried default values for start and end date params but I still have the same problem: whenever I select a date in the preview tab which can't be read as a valid US date I'm told that the value for the parameter is the not valid for its type. My report parameters and stored procedure parameters are set up as datetime; as it happens I don't need to display the parameter in the report. I've also tried creating a new report in the BIDS environment, taking all the approaches you suggest but the issue presented itself there as well.

Do you have any other suggestions?

Thanks,

Louise

Hi again,
I've solved this by avoiding the use of datetime as it just doesn't seem to work. A bit more Googling for "value provided for the report parameter is not valid for its type" made my mind up as I found several posts describing the same issue but no-one saying they'd found a cause and solved it. So I've the param values in the stored proc as nvarchar and handle the dates as CONVERT(CHAR(10), mytable.mydatecolumn, 102) then in the report specify string as the param type and the default values as before. It's frustrating that I couldn't use datetime types as I wanted but as it works fine I'm going to go with this.

Thanks again for your help.

Louise

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial