datepart parameter is not accepting multiple parameters in ssrs

Question

Hi,

i have a ssrs report which accepts month, year from user which are varchar values and i convert them into date in sql query and i use DATEFROMPARTS function to get the date formate.

it is filtering the report if only one values is passed but fails if multiple values selected for month or year.

sql used to convert is:

declare

@uyearmon date
select @yearmon =datefromparts(@uyear,@umon,1)

select * from table
where datecolumn in (@uyearmon)

please let me know if any additional info required.

Thank you

Answer 1

we are using sql server 2016 but DB compatibility is 120 .
STRING_SPLIT is available for the versions above 130 compatibility levels.

Thank you

Answer 2

Which SQL Server version are you using? Since 2016 you can use the STRING_SPLIT function to convert the CSV to a table result:
https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

Answer 3

Thank you OlafHelper-2800 ,

These are the values selected by user in the report parameters, can you please provide details how to handle this.

Answer 4

fails if multiple values selected for month or year.

Multiple parameter values are passed as comma separated list (CSV) to the SQL query; your query can't handle CSV, only one scalar value and so it fails.