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:
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....
I have a parm that is displaying in the drop down like:
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....
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)
ASKER
Can you provide me your rdl?
ASKER
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.Va lue,"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
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.Va
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?
ASKER
Hi Experts,
Ok,
When I do this:
So I end up doing:
In SSRS this makes no difference I still get:
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
Ok,
When I do this:
Select top 6 CAST(Scandate AS DATE) From Scandate
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
In SSRS this makes no difference I still get:
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
Hope it helps!
Report1.rdl
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
Hope it helps!
Report1.rdl
ASKER
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....
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great that works thanks
You need to cast it to DATE.
Please use like below-
Open in new window
Hope it helps!