Removing time from SSRS Date parameter
I want to remove the time from the parameter dropdown, NOT the cell referencing the parameter
I have a simple parameter weekEndingDate
served by mydataset
SELECT TOP (8) Convert(Date,FullDate, 101) AS FullDate
FROM DimDate
WHERE (DayNameOfWeek = 'Friday') AND (CAST(FullDate AS Date) < CAST(GETDATE() AS Date))
ORDER BY FullDate DESC
I have also tried
SELECT TOP (8) CAST(FullDate as Date) AS FullDate
FROM DimDate
WHERE (DayNameOfWeek = 'Friday') AND (CAST(FullDate AS Date) < CAST(GETDATE() AS Date))
ORDER BY FullDate DESC
The problem is that the options parameters still display the time.
If I run the query in Query Designer I get mostly correct output (Convert gives me m/dd/yyyy
instead mm/dd/yyyy
) and this is true for converting and converting, but the drop down parameter still has time and if I put the parameter in a cell it will also have time
I deleted .data files
I deleted and recreated the parameter, but did not expand or rebuild or nothing WITHOUT the parameter, I deleted and immediately recreated and then clicked Preview
I've tried both CAST and CONVERT
I have tried viewing, running and deploying a report
In all cases, time stays, and I am dumbfounded, everyone helps to evaluate, and I am glad to clarify anything.
source to share
Try setting the parameter data type to text instead of date / time.
Also needed to change dataset to return varchar
Target dataset code:
SELECT TOP (8) CAST(CONVERT(Date, FullDate, 101)as VARCHAR) AS FullDate
FROM DimDate
WHERE (DayNameOfWeek = 'Friday') AND (CAST(FullDate AS Date) < CAST(GETDATE() AS Date))
ORDER BY FullDate DESC
source to share
-
Open the report in SQL Server Business Intelligence Development Studio or Report Builder3.0.
-
Go to the Design tab, right-click the text box that will display the @Time parameter, select an expression.
-
Clear the expression dialog, then enter:
= FormatDateTime (options! Timer.Value, DateFormat.ShortDate) or = FormatDateTime (options! Timer.Value, 2).
source to share