SSRS - Format Output of Dataset for Input of Parameter

Question

I have a dataset that is driven by a stored procedure requiring a parameter. The parameter is DATETIME but I am unable to present in that way in the report because DateTime's show as a calendar and I need a dropdown of values.

Alas, I have a dataset that drives the choices for the parameter called BaselineDate. The issue comes down to the way that SSRS formats the DateTime value for the parameter then passes as VARCHAR to the stored procedure. The VARCHAR value is in the wrong format.

SSRS

Expected Value

2016-04-07 13:01:19.173

Update

Per @Marco Bong's suggestions I have converted the dataset that is driving the Parameter options to the proper format. Unfortunately, SSRS I passing the parameter value as null.

Below is what is a debug table I created. I am simply inserting the value of the parameter into this table. As you can see SSRS is passing null to the stored procedure as NULL which in theory should be impossible as the parameter is set to not allow nulls. Any ideas?

Let me understand what you are trying. You have a dataset that populates your parameter with datetime values but your parameter is text data type, right? — alejandro zuleta
Do you have access to SP code? I am pretty sure if you convert datetime to varchar it will be appear as you need. SSRS implicitely converts and format datetime column to text in order to populate the parameter. — alejandro zuleta
Also you can create table variable and populate it from your SP. INSERT INTO @MyTableVariable EXEC MyStoredProcedure. Then from the table CONVERT the column to VARCHAR. — alejandro zuleta
I could do that but the issue I have is at the SSRS level. I need the user to pick the Publish Date from a drop down then that value be used in the stored procedure. — ExceptionLimeCat
ou can create an internal parameter to pass the value to the SP and use your visible parameter to let user select the value then convert it again to a datetime and assign its value to the internal parameter which you have to pass to the SP =CDATE(Parameters!visibleBaseLineDate.Value) — alejandro zuleta

Marco Bong Marco Bong · Accepted Answer · 2016-04-13T00:26:18

select CONVERT(NVARCHAR,getdate(),21) as dtValue
//output will be ===> 2016-04-13 08:32:16.697

Update

If you want to use that selected value in another dataset (which execute your store prod), you may need to do this:

Then you can use this @param1 in your query.

Either where something = @param or set @baselineDate = @param

SSRS - Format Output of Dataset for Input of Parameter

1 Answers