CODEWITHSUNDEEP
t-sqlreporting-servicesparameterscasting
gruff
gruff

Reputation: 411

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

Upvotes: 5

Views: 15266

Answers (3)

High Plains Grifter
High Plains Grifter

Reputation: 1571

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.

Upvotes: 0

molleyc
molleyc

Reputation: 349

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

Upvotes: 4

xQbert
xQbert

Reputation: 35323

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).

Upvotes: 0

Related Questions