Wednesday, March 7, 2012
Argument data type nvarchar is invalid for argument 2 of dateadd function.
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.
Subscribe to:
Post Comments (Atom)
5 comments:
Thanks a lot - just saved me some valuable time!
thank's a lot for shared.
I used this for converting phone numbers to (###) ###-#### but needed to use bigint in order to avoid overflow.
format(cast(p.PhoneNumber as bigint), '(###) ###-####') as PhoneNumber
Just as I was about to lose the rest of my receding hairline I found this with the answer. Thanks for posting.
Post a Comment