5

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

Community
  • 1
  • 1
gruff
  • 411
  • 1
  • 9
  • 25
  • Possible Duplicate: http://stackoverflow.com/questions/16158469/excluding-the-time-in-a-date-time-parameter-for-ssrs-reports – xQbert Aug 07 '15 at 12:43
  • Instead of putting in an all-caps "this isn't a duplicate", try explaining why your situation is distinct from the linked question, and why the solution there doesn't apply. – Beofett Aug 07 '15 at 15:04
  • 1
    Have you tried setting your Parameter Data Type to Text instead of Date/Time? – molleyc Aug 07 '15 at 15:05
  • @Beofett, you are right, I rephrased the post to prevent future confusion – gruff Aug 07 '15 at 15:11
  • 1
    @Molleyc, that was a great idea! it didn't quite do it, but I recast the formatted date as varchar and that worked! please rephrase the suggestion as an answer and I will award you the correct response – gruff Aug 07 '15 at 15:12

3 Answers3

4

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
gruff
  • 411
  • 1
  • 9
  • 25
molleyc
  • 349
  • 2
  • 14
  • 1
    On its own this is a poor workaround, considering that the whole reason for using `date` columns is to leverage their semantics, e.g. being easily comparable (without `cast`ing back) to given start/end dates, etc. If SSRS gives us no option than to use the `varchar` kludge, you sdould return _two_ columns, one `cast`ed to text, and use the latter as the _label_ - but keep the parameter value as a proper date with semantics. – underscore_d Mar 09 '16 at 13:00
  • 1
    @underscore_d - that would make perfect sense if the question was about returning the actual data for display in a column on the report. It was simply about returning dates for selection from a parameter list. – molleyc Apr 04 '16 at 15:31
0

Sourced from: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/95d23f74-d7d3-41e1-8538-dbeff6e65ee2/ssrs-2008-r2-parameter-datetime-how-to-hide-time

  1. Open your report in SQL Server Business Intelligence Development Studio or Report Builder3.0.

  2. Click the Design tab, right-click the textbox where you will display the @Time parameter, select expression.

  3. Clear the expression dialog box, then type in:
    =FormatDateTime(Parameters!Timer.Value, DateFormat.ShortDate) or =FormatDateTime(Parameters!Timer.Value,2).

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • 1
    This would work, but I want the time removed from the Parameter Selection list, not a specific cell – gruff Aug 07 '15 at 12:44
0

the accepted answer does not provide a complete solution, since it changes the data type instead of the format of the parameter. Another solution, which I think has less drastic side effects is to make sure that you set a default date value, and format this to be a date rather than a date time, for instance:

=CDATE(FORMAT(DATEADD(DateInterval.Day,-30,Globals!ExecutionTime),"dd/MM/yyyy"))

This will result in the default date and subsequently selected dates from the datepicker to be displayed in a date only format, while still being a date. This is the method I use.

High Plains Grifter
  • 1,357
  • 1
  • 12
  • 36