Technology Answer: TSQL -- Inserting Dates Into Dynamic SQL

Consider the following TSQL:

SET @WhereClause1 = 'where a.Date > ' + @InvoiceDate

I get a date/string conversion error. @InvoiceDate is a datetime variable. What is the right syntax?

From stackoverflow

This might work.
```
SET @WhereClause1 = 'where a.Date > ''' + convert(varchar, @InvoiceDate) + ''''
```
eKek0 : this doesn't work because you have to enclosed the date (inside the string) with quotation marks

Andy White : Ok, thanks, I'll fix it

Jeff : with the '' correction this did what I needed. thanks all
Since your composing query as a string first, then I think you need to convert @InvoiceDate to a string with something like this. http://www.databasejournal.com/features/mssql/article.php/10894_2197931_1/Working-with-SQL-Server-DateTime-Variables-Part-Two---Displaying-Dates-and-Times-in-Different-Formats.htm
... and you will probably need to enclose date strings in quotes.

It would probably actually be better to construct the date string in the calling routine because you should be checking there for null values and maybe other validations.
This will work:
```
SET @WhereClause1 = 'where a.Date > ''' + cast(@InvoiceDate as varchar(100)) + ''''
```
Jeff : i wish I could mark two answers approved because this one works too. thanks

Andy White : +1 for getting it 100% right

eKek0 : When I have that trouble, I select one and give points (arrow up) to the others (if they are more than 1). That doesn't mean you have to do the same :)

EXEC sp_executesql N'SELECT * FROM Orders WHERE a.Date > @date',
                   N'@date datetime',
                   @date = @InvoiceDate

Technology Answer