TSQL -- Inserting Dates Into Dynamic SQL

Question

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?

Solution 1

This might work.

SET @WhereClause1 = 'where a.Date > ''' + convert(varchar, @InvoiceDate) + ''''

although an error will be raised if the value is null.

Solution 2

This will work:

SET @WhereClause1 = 'where a.Date > ''' + cast(@InvoiceDate as varchar(100)) + ''''

Solution 3

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

Solution 4

... 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.

Solution 5

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

View more solutions

Comments

[eKek0]

this doesn't work because you have to enclosed the date (inside the string) with quotation marks

[Jeff]

i wish I could mark two answers approved because this one works too. thanks

[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 :)