Situation:
I have build a query, using the DATEADD function, which executed without any error in SQL Server Management Studion (SSMS). I want to use this query as a dataset in my SQL Server Reporting Service Report (SSRS). I paste the query in the query designer window of my dataset. After I press the OK button of the Query designer, I got next error message:
Argument data type nvarchar is invalid for argument 2 of dateadd function.
This is the part of the query with the DATEADD function:
(SELECT DATEADD(HOUR, @MeasureHourStart, @MeasureDateStart) AS XXXX)
In my report I have report parameters @MeasureHourStart (INT) and @MeasureDateStart (DateTime)
Solution:
SSRS still sees @MeasureHourStart as a Varchar value.
As a workaround I forced to set the value of @MeasureHourStart as an Integer by using the CAST function.
(SELECT DATEADD(HOUR, CAST(@MeasureHourStart AS INT), @MeasureDateStart) AS XXXX)
Now the query will be accepted by SSRS as a valid dataset.
