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.

5 comments:

Anders V said...

Thanks a lot - just saved me some valuable time!

Hakan said...

thank's a lot for shared.

Hakan said...
This comment has been removed by the author.
Ian Todd said...

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

Peter said...

Just as I was about to lose the rest of my receding hairline I found this with the answer. Thanks for posting.