SQLTeam.com | Weblogs | Forums

Ssrs parameter to display date value only no time value


In an SSRS 2008 report, I want the user to be able to pick a date from the parameter called @requestedreceiveddate and have the default value set as todays date. This paramter is set to have a value of date/time.
The logic of the default value is the following: select cast(getdate() as date) as [Request Received Date] in a dataset called requestdate. and this works fine.

However when the user is able to select a requested received date from a list of values the calendar icon goes away and the drop down list values display date and time. I only want the date to be displayed. I am using the following sql:

SELECT DISTINCT personID, cast(value as date) as [Request Received Date],
FROM OPS.dbo.Customcustomer
wHERE attributeID = 2911
and personID = @personID
order by [Request Received Date]

Thus can you tell me how to have only a list of dates displayed in the dropdown list of dates that the user can select from?


It isn't intuitive, but as MS saw fit to lock down their parameter data types the easiest way I've found is to simply format the date as a string, set the parameter as 'Text' and go from there. As long as you have the date string formatted properly, SQL server knows what to do with it.
So - change your parameter to a Text data type and your SQL to something like this:

SELECT DISTINCT personID, LEFT(CONVERT(VARCHAR, value,  120), 10) as [Request Received Date],
 FROM OPS.dbo.Customcustomer
 WHERE attributeID = 2911
 and personID = @personID
 order by [Request Received Date]