Convert datetime to nvarchar in dynamic SQL statement
Posted on 2014-10-21
I'm migrating some queries from Access to SQL Server stored procedures.
Have one SP that is working great right now, but which requires me to build the SQL string dynamically. I'm trying to add a criteria to the string checks whether a date field is null or is less than a date that is passed into the procedure, but I am getting an error:
The data types nvarchar and datetime2 are incompatible in the add operator.
The line that is causing the problem is:
WHERE ((WF.Eff_Date IS NULL) OR (WF.Eff_Date <' + dateadd(day, 1, @EndDate) + '))
There are lines preceding and following this text which is why there is no opening or closing ' on this particular line. What I want this to look like when the code executes is:
WHERE ((WF.Eff_Date IS NULL) OR (WF.Eff_Date < '1014-10-22'))
Where the @EndDate value passed into the stored procedure is '2014-10-21'
I tried using & to set off the dateadd function, but that didn't work either.