Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Remove Time From SSRS Date Parameter

I want to remove the time from my Parameter Selection Dropdown, NOT a cell referencing the parameter

I have a simple parameter weekEndingDate which is fed by my dataset

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 issue is that the parameter options still display time.

If I execute the query in Query Designer I get basically correct output (Convert is giving me m/dd/yyyy, instead of mm/dd/yyyy) and this is true for cast and convert, but the parameter drop down still has time, and if I put the parameter into a cell, it also has the time

I have deleted the .data files

I have deleted and recreated the parameter, but did not deploy or rebuild or anything WITHOUT the parameter, I deleted and immediately recreated then hit Preview

I have tried both CAST and CONVERT

I have tried Previewing, Running, and Deploying the report

In all cases the time remains and I am dumbfounded, all help appreciated, and I'm happy to clarify anything

like image 527
gruff Avatar asked Sep 28 '22 02:09

gruff


1 Answers

Try setting your Parameter Data Type to Text instead of Date/Time.

Also changing the dataset to return varchar was necessary

Final 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
like image 137
molleyc Avatar answered Oct 03 '22 07:10

molleyc