Data type for parameters
-
I have a database that has a date field and a time field, both in datetime format. In the date field, all the times are 12am. Trouble is, users see a time in the parameter field and think they can narrow the report down to a specific time, but as soon as the time is changed from midnight, that whole day's data is eliminated from the report because it compares only on the date field.
Does anyone know a way to set a parameter to be only a date type instead of date/time? Or, as a workaround, is there some code I could use to combine the date and time fields so that the report parameters would accurately use the date and time entered?
I think my biggest complaint about Reporting Services is that they make it really hard to manipulate bad data. They just expect that you want to see what's really in the db. Silly silly silly.
Thanks in advance,
Casey
-
You could try using the datetime information supplied and passing the appropriate parts to each of your fields. e.g.
SELECT <some_stuff> FROM <my_Table> WHERE CONVERT(CHAR(8), <my_Date_Field>, 112) = CONVERT(CHAR(*), @The_Param, 112) AND CONVERT(CHAR(8), <my_time_field>, 108) = CONVERT(CHAR(8), @The_Param, 108)
Note that with the above, this will try to match to the millisecond, so prob not so useful. You could always trim it (ie using left(..., 5) but this could slow things down.
One way we limit people to using dates only is to specify the values that can be used (ie populate the parm with a list of values from a query). Then they have no choice re: time.
Steve.
-
Casey,
I found the best way was to make the Date parameter a string, like... 10-Jul-2005.
I could educate my small group of users to enter this the correct way, so this was easy for me.
When using a DateTime type, users tried to change the datetime (including milliseconds!!!) and always seemed to mess something up.
Hope this helps.
Simon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply