Convert a date to integer within parameter

Question

Right now I have a Stored procedure which takes a date as an integer (140213) but within SSRS I need to convert the date selected within the date parameter to the integer mentioned before.

Right now I am trying to achieve this by having 2 parameters (date and convert), one that lets you select a date, and the 2nd trying to convert the date to an int. Within the 2nd parameter in the Default value I have:

=FormatNumber(Format(Parameters!date.Value, "yyMMdd"),0)

The 2nd parameter is set to internal and the data type set to integer.

When I try to run this report I get the error

The property 'DefaultValue' of report paramater 'convert' doesn't have the expected type.

Any idea on how this can be achieved?

Answer 1

The optimal solution is to modify the stored procedure to accept a date/datetime parameter. This prevents unnecessary conversion and confusing representation of dates with single digit years as an integer type (e.g. 2009-01-01 -> 90101)

Assuming you have no control over the stored procedure definition, you can still achieve what you want using the steps below. These assume existence of two parameters:

• Date (user input from SSRS date control)
• ConvertedDate (integer representing converted Date value)

Steps

1. Set the ConvertedDate Data Type to Integer and parameter visibility to Internal

2. Set the ConvertedDate default value to Specify Values using expression:

   =CInt(Format(Parameters!Date.Value, "yyMMdd"))

   

3. ConvertedDate parameter value can now be passed to stored procedure. Sample report output with Date selection and ConvertedDate value: