March 24, 2009 at 9:48 am
Description:
I am trying to create a parameter-driven report with a datetime drop down parameter. The drop down allows me to select and run a report for a datetime as long as the datetime does not have a millisecond value other than 000. With the test data below, the two values appear in the drop down but when I try to select the first value and click on View Report, it rejects the value without an error message and asks me again to "Select a Value"
Data:
CREATE TABLE TestTable(DateandTime datetime)
INSERT INTO TestTable VALUES('2009-02-02 11:22:33.003')
INSERT INTO TestTable VALUES('2009-02-02 11:33:44.000')
Dataset ds_Date:
SELECT DateandTime from TestTable
Parameter prm_Date:
Data type: DateTime
All Checkboxes: Unchecked
Available values: From query
Dataset: ds_Date
Value field: DateandTime
Label field: DateandTime
Default values: Null
Report Body:
A single textbox with the parameter as an expression "=Parameters!prm_Date.Value"
The database is SQL 2005 SP2, BIDS and Server are up to date. Can anyone reproduce this problem? Thanks for checking, C
[EDIT] Removed connfused smiley
March 24, 2009 at 1:31 pm
I have confirmed that this happens this way in my environment as well. I'm using Sql 05 SP3.
It works correctly when you define the parameter as a string.
Since you're prepopulating a drop down list anyhow, there should be no real difference between a string and a datetime parameter. Your validation is handled by your query and you don't get the availability of the calendar control with both methods...
Will using a string param work for you, or is this just a test setup to confirm what is happening when you do something else with a datetime+ms?
-Luke.
March 25, 2009 at 3:21 am
@luke. Thanks for your reply and confirmation.
Unfortunatley it does not work correctly when I define the parameter as a string. It appears that it is accepted but the result is that the milliseconds are removed when the prm_Date is used as a parameter in a query. And this is not the intended behaviour.
You will see this if you add a main dataset with prm_Date as a parameter in the where clause and just dump this somewhere in a textbox in the report.
exec sp_executesql N'SELECT DateandTime FROM TestTable WHERE DateandTime = @prm_Date',N'@prm_Date nvarchar(19)',@prm_Date=N'02.02.2009 11:22:33' << no milliseconds Bummer!
Furthermore bad things happen if you live in a country that uses DMY and you testdata has a day number above 12.
exec sp_executesql N'SELECT DateandTime FROM TestTable WHERE DateandTime = @prm_Date',N'@prm_Date nvarchar(19)',@prm_Date=N'13/02/2009 11:22:33' << Which month is 13!
More strangeness:
Give prm_Date a default value from a query "SELECT TOP 1 DateandTime FROM TestTable"
Leave the parameter as a DateTime
Run the report. It will run just fine the first time automatically(in BIDS):
exec sp_executesql N'SELECT DateandTime FROM TestTable WHERE DateandTime = @prm_Date',N'@prm_Date datetime',@prm_Date='2009-02-02 11:22:33:003'
Now click on View Report, and watch how now all of a sudden it behaves just like in my first post.
This was just a test setup, but string does not pass the milliseconds anyway. And it is just dirty and pain to use strings to overcome the DMY YMD stuff. I like my reports to work anywhere with Language set to "=User!Language".
I reckon Datetime in SSRS is broken, plain and simple. Surprisingly despite a lot of googling I seem to be the only person who mentions this or suffers from this problem.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply