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?
+1
A:
This might work.
SET @WhereClause1 = 'where a.Date > ''' + convert(varchar, @InvoiceDate) + ''''
Andy White
2009-04-02 05:04:40
this doesn't work because you have to enclosed the date (inside the string) with quotation marks
eKek0
2009-04-02 05:12:43
Ok, thanks, I'll fix it
Andy White
2009-04-02 05:14:39
with the '' correction this did what I needed. thanks all
Jeff
2009-04-02 05:19:21
+1
A:
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
AaronLS
2009-04-02 05:05:30
+1
A:
... 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.
le dorfier
2009-04-02 05:07:33
+4
A:
This will work:
SET @WhereClause1 = 'where a.Date > ''' + cast(@InvoiceDate as varchar(100)) + ''''
eKek0
2009-04-02 05:11:43
A:
EXEC sp_executesql N'SELECT * FROM Orders WHERE a.Date > @date',
N'@date datetime',
@date = @InvoiceDate
Anthony Faull
2010-04-26 19:32:37