SQLTeam.com | Weblogs | Forums

Ssrs parameter to display date value only no time value


#1

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?


#2

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]

Craig