Convert datetime to nvarchar in dynamic SQL statement

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.
LVL 51
Dale FyeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Shouldn't it just read:

WHERE ((WF.Eff_Date IS NULL) OR (WF.Eff_Date < dateadd(day, 1, @EndDate) ))

as DateAdd returns a date value.

/gustav
0
Dale FyeAuthor Commented:
Gustav,

When I left all of that code inside the single quotes that define the string, it gave me an error with the @EndDate parameter. Cannot remember exactly what the message was, but I assumed that the exec line could not properly execute the dynamic string because @EndDate is not defined within the dynamic SQL.

Dale
0
Gustav BrockCIOCommented:
Hmm. I thought @EndDate was defined as a parameter.
Not quite sure what you are doing.

/gustav
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>requires me to build the SQL string dynamically.
>WHERE ((WF.Eff_Date IS NULL) OR (WF.Eff_Date <' + dateadd(day, 1, @EndDate) + '))
Since you're using Dynamic SQL, when concatenting a string that also uses non-string values such as dates or numbers you'll need to CAST any other data type (such as date) to a char value

WHERE ((WF.Eff_Date IS NULL) OR (WF.Eff_Date <' + CAST(dateadd(day, 1, @EndDate) as varchar(25)) + '))

Open in new window

0
lluddenCommented:
You need to convert the date to text, then put that in single quotes.  

@SQL = 'WHERE ((WF.Eff_Date IS NULL) OR (WF.Eff_Date <''' + CONVERT(varchar(10),dateadd(day, 1, @EndDate),110) + '''))'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeAuthor Commented:
Thanks, guys.

I ended up using Cast, but then still had to add in the additional single quotes.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.