Solved

Convert datetime to nvarchar in dynamic SQL statement

Posted on 2014-10-21
6
654 Views
Last Modified: 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.
0
Comment
Question by:Dale Fye (Access MVP)
6 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Hmm. I thought @EndDate was defined as a parameter.
Not quite sure what you are doing.

/gustav
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
Comment Utility
>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
 
LVL 18

Accepted Solution

by:
lludden earned 400 total points
Comment Utility
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
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
Comment Utility
Thanks, guys.

I ended up using Cast, but then still had to add in the additional single quotes.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now