Link to home
Start Free TrialLog in
Avatar of Amour22015
Amour22015

asked on

SSRS 2012 r2 - Parm Drop Down has Date/Time

Hi Experts,

I have a parm that is displaying in the drop down like:
User generated image
I want it to display like:
1-1-2017
etc...

I need to take out the time.  I have tried text/date time.

This comes from a dataset
Scandate

The parm points towards The Dataset Scandate:
Which is:
Select top 6 Scandate From Scandate
this gives the last 6 Scandates for the dropdown.


Please help and thanks....
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi,

You need to cast it to DATE.

Please use like below-

Select top 6 CAST(Scandate AS DATE) Scandate  From Scandate

Open in new window


Hope it helps!
Trial for you-

/*------------------------
SELECT GETDATE()

SELECT CAST(GETDATE() AS DATE)
------------------------*/

-----------------------
2017-02-05 21:04:36.893

(1 row(s) affected)


----------
2017-02-05

(1 row(s) affected)

Open in new window

Avatar of Amour22015
Amour22015

ASKER

Hi Experts,

Ok when I did this:
Select top 6 CAST(Scandate AS DATE) Scandate  From Scandate

Open in new window


I can see that it works in SSMS

But when I put this into SSRS

I still get:
User generated image

Please help and thanks
Can you provide me your rdl?
Hi Experts,

Also I should point out:  this is not a field in SSRS this is a passed parm from the dataset Scandate that has the query.
leaving me no option to do this:
=Format(Fields!Scandate.Value,"dd/MM/yyyy")


Also note that other reports in SSRS use this same query (without Cast(Scandate as Date) from SSMS and the dropdown works fine in the other reports.

I have tried:
Data type:
Text and DateTime in the Report Parameter Properties under General
I have Available Values set to: Get vales from a query which is:
Dataset: Scandate
Value field: Scandate
Label field: Scandate

Default Values: "No default value"
 in
SSRS

Please help and thanks
Can you pls provide me the query used to fill dataset Scandate?
Hi Experts,

Ok,
When I do this:
Select top 6 CAST(Scandate AS DATE)  From Scandate

Open in new window

I get No Name in the column.

So I end up doing:
Select top 6 CAST(Scandate AS DATE) Scandate  From Scandate order by Scandate Desc

Open in new window


In SSRS this makes no difference I still get:
User generated image
Which makes me think the problem is in the SSRS report .rdl
and because other reports use this same type query without the Cast/Date involved looks like that again the problem is within the SSRS report.

I started out by copying another .rdl , that was close to what I was looking for maybe I need to recreate from start.

Please help and thanks
Can you send me your rdl. I shall fix it.
Hi Amour22015,

Please change your query to below-
SELECT CONVERT ( VARCHAR(10), Scandate , 101 ) Scandate  FROM Scandate

Notes by Pawan
1. If the datetime column is coming from the database then SSRS will show it in date time format. E.g. 2017-02-07 09:56:40.007 from database , in SSRS you will get 2/7/2017 09:56:40.007 AM
2. If the date without time column is coming from the database then SSRS will append the default time with it.  E.g. 2/7/2017 - from database , in SSRS you will get - 2/7/2017 12:00:00 AM

Fresh RDL-
I have created a fresh rdl with sample data and the expected output. <Report1.rdl>

Screen shot output coming from my rdl
Below is the screen of the expected output
User generated image
Hope it helps!
Report1.rdl
Hi Experts,

I have tried:
SELECT CONVERT ( VARCHAR(10), Scandate , 101 ) Scandate  FROM Scandate
But note I have to include the 6 month:
SELECT Top 6 CONVERT ( VARCHAR(10), Scandate , 101 ) Scandate  FROM Scandate

When I do this I get random dates like:

2014-10-01
2014-09-01
2015-11-01
2016-12-01
etc...

that is why I backed out of this.

Please help and thanks....
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Great that works thanks