Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Convert datetime to nvarchar in dynamic SQL statement

Posted on 2014-10-21
6
Medium Priority
?
874 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
6 Comments
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40394898
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 49

Author Comment

by:Dale Fye
ID: 40394916
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 52

Expert Comment

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

/gustav
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 400 total points
ID: 40394945
>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 1600 total points
ID: 40395220
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 49

Author Closing Comment

by:Dale Fye
ID: 40395719
Thanks, guys.

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

886 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