SSRS - Is there a way to restrict date/time input parameters to date only? (Not report output field format.)

Question

I am writing an SSRS report that has several parameters including a couple of date fields. I do not want the user to be able to enter time information in either date field, but SSRS only has the Date/Time data type. Is there a way to force these report parameters to act as date only, and can I set a specific format (e.g., dd/mm/yyyy)? I would like to keep the built-in date-picker-calendar functionality.

I do not want to write my own report parameter web page because if I did then this one report would be the odd one out given that all of our other reports (which don't use date parameters) work fine with the built-in SSRS parameter entry functionality.

Perhaps the answer is that you can't do it with the built-in options, but that seems crazy - how could something so obvious have been overlooked?

The Google and Stackoverflow searches I've done only gave me ways to set the format in the report output (actually there are a number of cases where people have asked a question similar to mine and only received answers about setting the output format).

Answer 1

There is a way, but it will require you to CAST your date as Varchar(10) in your parameter dataset.

Next,You'll have to choose "Data Type:" as Text under Parameters section.

Again, you'll have to make sure your SQL code re-converts it into date again. I do not prefer this way, but users really wanted to see date without time.

Let me know if you'd like screenshot or more detail.

Answer 2

The problem is that you are using parameters that get timestamp information.

For example, if you are using Now() in your expressions- you will be asking for the current date AND the current time. However, if you use Today()- you will only be asking for the current date.

=Today() 'returns date only

=Now() 'returns date and current timestamp

Useful references:

http://msdn.microsoft.com/en-us/library/ms157328(v=sql.90).aspx

http://msdn.microsoft.com/en-us/library/ms157328.aspx

Answer 3

To answer my own question based on research I've done since asking it: it seems it is not possible to control how SSRS handles user entry of date/time values in parameters. If there is a need to restrict to date (or time) only or do cross-field validation then you need to implement your own front-end - which unfortunately for my specific business case doesn't help.

Answer 4

In my experience it has only given me the date and time when the filed you are selecting from contains both date and time. I have found if I am selecting on a date only field then I only get the date in the parameter